Errors returned by expressions in SQL queries are not necessarily the same as the errors returned by equivalent PL/SQL expressions.

Have you ever noticed that error codes change depending on whether the context is SQL or PL/SQL?

DECLARE
    x number := 0;
    y number;
BEGIN
    select log(10,x)
      into y
      from dual;
END;
/

The PL/SQL block above returns following error:

ORA-01428: argument '0' is out of range
ORA-06512: at line 5

But if I change the way I assign the value to y, the error will be much more generic.

DECLARE
    x number := 0;
    y number;
BEGIN
    y := log(10,x);
END;
/

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5 

Now, this is a trivial case, but imagine a situation where you initially wrote the code in a certain way and then it turns out you have to change completely the approach for some reason, a business request, code refactoring, whatever.
If there is an EXCEPTION block catching a specific error, ORA-01428 for instance, after the change it won't catch that error any longer, presumably with some consequences for the final outcome of the procedure or function.

End loop statement can raise ORA-06502 too

I was puzzled when I got an error message allegedly occurring at a line containing an "end loop" statement and it took me a while to figure out that this occurs when either bound of the loop is NULL.

In my case both the initial and final bounds are variables and they were supposed to be not null or so I thought...

Here is a code snippet reproducing the error:

begin
  for i in null..100
  loop
    null;
  end loop;
end;
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

So, if you see this error reported at this unusual location, you know what you are up against.

ORA-06502 when deinstalling apex supporting objects

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

It took me a while to figure out why i was getting this strange error when attempting to deinstall the supporting objects of an Apex application:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Unable to deinstall application.

I had the suspect there was something wrong with the statements being executed but after several attempts to understand which statement was the culprit, i turned to the application file dump to see if i could spot something in there.

Indeed this is what i found:

Highlighted in grey color you can see some junk characters at the beginning of the deinstallation script.
As the simple deinstallation script was assembled from copying and pasting ddl statements taken from a SQL Developer window, i imagine that, at some point, something must have gone wrong.

The tricky bit is in that neither from the apex editor nor from the deinstallation script preview page you spot anything strange, those characters are nowhere to be seen.

So, if you are getting the error above, check out the application file dump with a text editor!

See more articles about Oracle Application Express or download tools and utilities.

Harry Potter and the scary secret of the five cursed error numbers

Will Mrs. Rowling ever forgive me for borrowing a couple of paragraphs from one of her books and stuff them with double (or triple) meanings?

"DBMS_OUTPUT messages were still lashing the windows, which were now minimized, but inside something looked wrong and awful. The monitor glowed over the countless office chairs where programmers sat typing, talking, doing their work or, in the case of Fred and Barney, trying to find out what would happen if you fed a handful of bizarre numbers to UTL_LMS.GET_MESSAGE.
Fred had "rescued" the enigmatic, five-parameters function from an Elements of Almost Undocumented Oracle Packages class and it was now testing it intensively on a table surrounded by a knot of anonymous bloggers."

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_LMS", line 4
ORA-06512: at line 9
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:
*Action:

A couple of days ago i wrote a little program using the UTL_LMS.GET_MESSAGE packaged function on Oracle 10G R1 and i came across this weird situation:
looping on all error numbers between 1 and 50000, the function always returns zero (success) as return code, even for non-existent error messages, but there are 5 numbers (the magical ones) that will cause the function to blow up with an ORA-06512 exception and they are:

33422 35982 36188 36906 36976

Oddly enough, the documentation says that when the procedure fails it should return -1, but i could never get such value for any error numbers in the aforementioned range, given 'rdbms' as product and 'ora' as facility.

33422, 35982 and 36906 do not appear in the official documentation, but 36188 and 36976 do.

However, while reading the description for ORA-36976 and to my utter dismay, i realized soon that the reason of the failure of UTL_LMS.GET_MESSAGE was less than mysterious:
i had simply undersized the receiving variable for the error message description, that in just these 5 cases can be longer than 255 characters!

To my discharge i must say that i sized the variable just a little bit bigger than what suggests the example section of the function (by the way, there is also some syntax problem in the sample code for the other packaged function called FORMAT_MESSAGE, written with triple S....), but the "stress-testing" of this function showed that a no-brainer value like 1024 will make it work in all cases.

In conclusion, i learned a couple of lessons:

1. never rely on the sizing of variables given in the package examples
2. never forget that ORA-06512 may be a symptom of an undersized string variable passed as parameter.

Too bad, even for Harry Potter ;-)

PS: In case someone takes this posting too seriously, please note that these error numbers are showing up on 10G R1 (Windows) only, on 11G (Windows) they become six and they are 19151, 19193, 33422, 35280, 35982 and 36188...

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.