Wednesday, May 16, 2007

PLS-00307: too many declarations of XYZ match this call

While doing some tests before publishing my previous posting, i came across the following exception that is to be rarely seen when calling overloaded procedures (or functions) like this APEX_MAIL.SEND:

declare
var_addressee varchar2(255) := '...';
var_sender varchar2(255) := '...';
var_title varchar2(80);
begin
apex_mail.send(
p_to => var_addressee,
p_from => var_sender,
p_subj => var_title,
p_body => null);
end;

ORA-06550: line 6, column 1:
PLS-00307: too many declarations of 'SEND' match this call
ORA-06550: line 6, column 1: PL/SQL: Statement ignored

The problem is highlighted in purple.
Since APEX_MAIL.SEND comes in two versions (in other words it is an overloaded procedure), if we pass null to parameter p_body, the parser cannot decide whether it should reference the function version accepting p_body as VARCHAR2 or the version accepting p_body as CLOB.

The workaround is to replace null with some more meaningful expression.
Note that in general it could be enough to store null into a variable of the desired type, but this is not the case of APEX_MAIL.SEND that would just return the error i described in my previous posting.

5 comments:

Anonymous said...

You're a legend mate, I was stuck on this one for ages, the script worked in SQLPLUS but failed with this error in Forms. I was pondering how to extract all the bloody code and put it in a package... but you saved me, thanks!
Steve White

Rich J. said...

I've just discovered this page via Google. It led me to find that the same error can occur if a procedure was "accidentally" overloaded -- when the datatypes in the declaration of a proc/function don't exactly match between the package and package body.

Thanks for the pointer! Good to keep in mind when I start emailing reports from APEX!

Rich

Louis-Guillaume Carrier-B├ędard said...

Hello,
Thanks for the explanation.

Regards,
Louis-Guillaume

Anonymous said...

this doesn't work with the md5 functions in the obfuscation toolkit. However, I think what needs to be done is explicitly declare the type going into the function. I don't know how to do that between raw and varchar though....

Byte64 said...

I guess you are having the problem because you are calling the MD5 function or procedure without named parameters.
Try the difference:

declare
s varchar2(255) := 'abcdefg';
e varchar2(255);
begin
e := dbms_obfuscation_toolkit.md5(s);
end;

ORA-06550: line 5, column 7:
PLS-00307: too many declarations of 'MD5' match this call


declare
s varchar2(255) := 'abcdefg';
e varchar2(255);
begin
e := dbms_obfuscation_toolkit.md5(input_string => s);
end;

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles