[Chapter 10] 10.5 Miscellaneous and Minor Improvements

10.5 Miscellaneous and Minor Improvements

The Oracle PL/SQL development team has been busy! In addition to all of the other features covered in this and other chapters, they have also improved PL/SQL in the following ways:

Optimization of the STANDARD package's built-in programs. A lot of our code calls these low-level programs (TO_CHAR, SUBSTR, INSTR, etc.), so Oracle has focused on improving the performance of this package.
Faster anonymous block execution.
Faster RPC parameter passing.
Caching of dynamic link libraries (DLLs) for improved external procedure performance. External procedures can now cache up to 10 DLLs in a session; subsequent calls to a C function in a cached DLL will not cause the DLL to be reloaded.

And there's more!

10.5.1 Dramatically Higher Limits on Body Size

You will also be very glad to know that the maximum size of package and object type bodies is now greatly increased. Prior to Oracle8 i , the limitation on program size was determined by the maximum number of nodes supported in internal parsing tree structures: 2¹⁵ . This translated to a maximum byte size of approximately 128K (sometimes much less).

With Oracle8 i , the compiler will now support up to 2²⁶ nodes in its internal tree structure, giving us room to grow our code to something like 16MB! Of course, we will probably hit other limits before our code gets to be that large anyway.

10.5.2 Improved ORA-06502 Error Messages

Oh, that annoying ORA-06502 error! How many times have we seen this message:

ORA-06502: numeric or value error

only to wonder whether the error was caused by an attempt to put too large a string into a character variable or an attempt to stuff a non-numeric value into a numeric variable?

Now, the PL/SQL runtime engine will let you know for sure:

/* Filename on companion disk: ora6502.sql */
DECLARE
   this_world VARCHAR2(5);
BEGIN
   /* No room for justice... */
   this_world := 'Justice';
END;
/

ORA-06502: PL/SQL: numeric or value error: 
   character string buffer too small

DECLARE
   bills_fortune NUMBER;
BEGIN
   bills_fortune := 'UNIMAGINABLE';
END;
/

ORA-06502: PL/SQL: numeric or value error: character to number conversion error