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.
at

4 comments:

  1. AnonymousFriday, 10 August, 2007

    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.

    ReplyDelete
  2. Byte64Friday, 10 August, 2007

    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

    ReplyDelete
  3. AnonymousTuesday, 08 July, 2008

    Hello:

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

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

    ReplyDelete
  4. Byte64Tuesday, 08 July, 2008

    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

    ReplyDelete

I appreciate your comment however bear in mind that I might not have the time to reply soon.
Normally I do not reply to all comments but I am glad if you found something useful or if you learned something new, in that case I strongly encourage you to promote the article with the +1 google button.
Flavio