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.
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!
ReplyDeleteSteve White
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.
ReplyDeleteThanks for the pointer! Good to keep in mind when I start emailing reports from APEX!
Rich
Hello,
ReplyDeleteThanks for the explanation.
Regards,
Louis-Guillaume
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....
ReplyDeleteI guess you are having the problem because you are calling the MD5 function or procedure without named parameters.
ReplyDeleteTry 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;