Wednesday, May 16, 2007

ORA-06502 with APEX_MAIL.SEND

Just a quick comment about an elusive error message that you may get when using a built-in procedure for sending emails from Oracle Application Express (APEX).

I am referring to APEX_MAIL.SEND, Apex version 3.0, but probably it affects also the previous versions.

I have a simple procedure call like:

begin
...
apex_mail.send(
p_to => var_addressee,
p_from => var_sender,
p_subj => var_title,
p_body => var_message);
...
end;

If the content of var_message is null, you'll get this fairly generic error:

ORA-06502: PL/SQL: numeric or value error

If you are building the message body dynamically, you must ensure that the value passed to the parameter p_body is not null, using function NVL perhaps, for delivering a message in pure Magritte style as follows:

begin
...
apex_mail.send(
p_to => var_addressee,
p_from => var_sender,
p_subj => var_title,
p_body => nvl(var_message,'message body is empty')
);
...
end;
Or any other message that you like best.

4 comments:

Anonymous said...

You have to send a value for all the variables in the apex_mail.send procedure. that is why you're getting the error.

try using nulls for the bcc_v and cc_v and non_html_body_v
vars.

Byte64 said...

If it were as you say, i wouldn't get an email ever4 hours since 2 months, because that's the way i am calling this procedure in my pager application.

Moreover if you look at the package specification, you will see that all those parameters have a null as default value, so passing a null value wouldn't change much the situation.

Bye,
Flavio

Anonymous said...

Hello:

I cannot get APEX_MAIL.SEND to work with HTML text via a scheduler ..

http://forums.oracle.com/forums/thread.jspa?messageID=2633777�

Byte64 said...

Hello,
I've just replied on the forum, i believe the problem is in the function call where you pass just one parameter defined as CLOB, whereas you should define both p_body and p_body_html as CLOB.
See also this article on PLS-00307.

Bye,
Flavio

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