Monday, January 14, 2008

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"

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


Patrick Wolf said...

Hi Flavio,

the first link to "UTL_LMS.GET_MESSAGE" points to your Oracle documentation on localhost.


Byte64 said...

Hi Patrick,
thanks for reporting this!


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