Today I received a strange error from Oracle, for what appeared to be no good reason:

1. ORA-06552: PL/SQL: Compilation unit analysis terminated
2. ORA-06553: PLS-320: The declaration of the type of this expression is incomplete or malformed

I received the error when attempting to create a trigger on a newly created table. I could create triggers on other tables without any errors, however on the new table – it would error every time.

The table definition included a column named timestamp with the data type timestamp. Oracle was happy enough for me to create the table, however it refused to let me create a trigger on the table with a column name over lapping a reserved word.

The solution was simple, renamed the column in question to use a non-reserved word and everything continued as normal. I must say though, I think it is strange that it would let me create the table using the reserved word in the first place if it was going to complain about me using other standard Oracle features which would error as a by product of the reserved word.

Today I came up against a very frustrating problem when writing some Oracle PL/SQL stored procedures and functions:

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

1. FUNCTION MyFunction
2. (pData IN VARCHAR2)
3. 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:

1. FUNCTION MyFunction
2. (pData IN CLOB)
3. 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 NULL value to a variable defined as NOT NULL
• assign a value greater than 99 to a variable defined as NUMBER(2)
• assign a CLOB with a length greater than 32767 bytes to a VARCHAR2 variable

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.