Wednesday, August 14, 2013

Old Oracle APEX "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" Error

Oracle Application Express 3.1.0.00.32

I added some display items to a large page and all of sudden I started getting "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" when I attempted to save changes.

Too make a long debug process short, I discovered that this was an error generated internally by APEX code not in any application code.  Because I had too many items on the page I assume that APEX is assembling an INSERT statement in a local PL/SQL variable somewhere internal to APEX and the INSERT text string is too large in length and cannot be assigned to the local variable.  If I cut the number of items down to 118 items the pages runs fine.

I also noticed a very small message at the bottom of the items region of the page definition that says, "Item count exceeds maximum of 100."  It looks informational and does not stop you in any way.  It would be nice if that were an actual alert that popped up and said don't do this or else you will get a runtime ORA-06502 error upon submitting the page.

I am posting this for my recollection and all others who are working in an old version of APEX like Application Express 3.1.0.00.32 and encounter this error.  I have searched multiple times on the Internet and could not find any references to this.