March 24, 2014
Posted by on
I ran into a irritating bug while executing a packaged procedure a few days ago. Intially, it seemed that the was running into errors randomly but then I ran a few test cases and noticed the following:
- The first time I execute the procedure after recompiling the package, it invariably throws an exception
- On executing the procedure after this first run, no more exceptions are raised
After examining the code, I identified a global variable declaration in the package body as a possible culprit since the size of the variable was less than the length of actual string being stored.
g_module_name VARCHAR2(1) := 'XX_PROCESS_UTIL';
A few frenzied minutes of reading later, I knew what was happening:
The first time I executed the packaged procedure, Oracle tried to initialize the package (including all global variables) and threw an ‘ORA-06502: PL/SQL: numeric or value error: character string buffer too small’ error when it encountered the above line of code. However, even though the package was not initialized properly it was internally marked as initialized so when the same package procedure was executed again Oracle skipped the initialization part(including the above line of code) and hence did not throw the error. But this time the apparently error-free execution of the package hides a potentially fatal flaw – the variable g_module_name has the value NULL instead of XX_PROCESS_UTIL since it was never set to XX_PROCESS_UTIL because of the ORA-06502 error.
Since a package is reinitialized whenever a new session is started or when the package is recompiled, so every time I recompiled the package and ran the procedure it again threw an error.
Steven Feuerstein provides a more comprehensive explanation of this issue and the best practices to avoid this here.