Weird ORA-6502 errors from incorrectly sized PL/SQL package variables

29 Nov 2011 by Simon Haslam (in General)

This is a rather unusual post for a middleware administrator, but I felt I had to write it up as I'm embarrassed to say it took me over a day to get to the bottom of this problem, and couldn't find anything helpful on MOS or via Google. Therefore hopefully someone in a similar posistion may stumble upon this post and find at least some hints.

The problem was with some changes I'd made to a very stable and mature system built by Veriton around 10 years ago. The first piece of PL/SQL code affected hadn't been changed for a very long time but was running some fancy SQL calling a remote database (running a different Oracle version) over a DB link... but, despite me doing 10046 traces, none of that was relevant. The essence of the issue are two observations:

• for a constant defined in a PL/SQL package the initialisation only happens at run-time,
• an initialisation failure of one variable in a package may manifest itself when you access a completely different variable in the same package.

In this application a single package is used to reduce hard-coding in PL/SQL and SQL statements, or at least to centralise codes to one place, as well as improve legibility. Note the constants are static so don't warrant their own lookup table in this case, especially given the overhead that would mean elsewhere in the code.

To illustrate the problem, let's create a simple package that contains two constants:

CREATE OR REPLACE PACKAGE simon_test IS
    C_GOOD_PKG_CONSTANT  CONSTANT VARCHAR2(4) := 'ABCD';
    C_BAD_PKG_CONSTANT   CONSTANT VARCHAR2(3) := 'ABCD';
END simon_test;
/

CREATE OR REPLACE PACKAGE BODY simon_test IS
BEGIN
    NULL;
END simon_test;
/

You will notice that C_BAD_PKG_CONSTANT is only defined as 3 characters long and not 4, however you don't get a compile-time error:

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 29 20:54:47 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @simon_test

Package created.

Package body created.

SQL>

What is annoying is that you do get a run-time exception, the first time a session tries to access any variable (or probably procedure, although I didn't test that) in the package:

SQL> set serverout on

SQL> exec dbms_output.put_line('Value of constant is: <'||simon_test.C_GOOD_PKG_CONSTANT||'>');

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.SIMON_TEST", line 3
ORA-06512: at line 1

SQL> /
Value of constant is: <ABCD>

PL/SQL procedure successfully completed.

SQL> /
Value of constant is: <ABCD>

PL/SQL procedure successfully completed.

SQL>

Bizarre!

Note in particular that I am accessing a completely different variable to the one that is defined incorrectly. I haven't found any clues to this behaviour in the PL/SQL documentation. You'll also spot that at least the line number in the exception was correct, however due to the nature of this particular system I only had a line number from a higher layer in the call hierarchy (which is how it used to be before DBMS_UTILITY.FORMAT_ERROR_BACKTRACE came along in 10.1) so that didn't help me. Curiously if you then recompile the package in the same session you don't get the ORA-6502 until you try to access the bad variable.

I later discussed this with friend, and frequent work colleague, Julian Dyke and his theory is that it's to do with way the PL/SQL compiler moves code around to separate out the declarations (on the stack) from the initialisation. We did have a play around with a parameter new to me called PLSQL_OPTIMIZE_LEVEL but that had no effect on this behaviour. I was testing on both 11.2 and 10.2, though it would be interesting to see if it has always been this way.

Anyway, this trivial error was quite a challenge to find but then could be ultimately distilled down to a simple test case... at which point you wonder where all the time went!