Today I came up against a very frustrating problem when writing some Oracle PL/SQL stored procedures and functions:
ORA-06502: PL/SQL: numeric or value error
When I first wrote the stored function in question, I was using VARCHAR2 types for storage since it was the data type returned by an Oracle provided package. The function signature looked something like the following example:
FUNCTION MyFunction(pData IN VARCHAR2)RETURN VARCHAR2;
The stored procedures and functions in question were being used with a lot of character information. Whilst running small sets of test data through the functions, everything was acting as expected. Unfortunately, as the test data sets increased in size I began to receive the ORA-06502 error.
As you may or may not be aware, within PL/SQL a VARCHAR2 type can store a maximum of 32767 bytes of information. When the ORA-06502 exceptions where taking place, this limit was being exceeded.
The thing that was so frustrating about the error, was that it wasn’t helping me identify the problem. In this particular instance, I had refactored a significant amount of PL/SQL and during that process changed some variables from VARCHAR2 into CLOB data types.
Oracle will allow you to pass a CLOB variable into a function that accepts a VARCHAR2, so long as the length of the CLOB is less than the maximum byte limit of the VARCHAR2. Since that wasn’t throwing a type conversion error in normal circumstances, it wasn’t something that I went looking into immediately as a possible problem when the Oracle ORA-06502 errors were thrown.
The solution to this particular problem is what you would expect, change the data types of all associated functions and procedures to use the CLOB data type:
FUNCTION MyFunction(pData IN CLOB)RETURN CLOB;
After looking into the error in more depth, it can be thrown for virtually any generic constraint violation. The following simple examples would produce this error code:
- assign a
NULLvalue to a variable defined asNOT NULL - assign a value greater than 99 to a variable defined as
NUMBER(2) - assign a
CLOBwith a length greater than 32767 bytes to aVARCHAR2variable
I think it would have been a little more useful to a developer for Oracle to throw some sort of a type conversion error in this instance.
