When initialization fails

Thereare several steps to initialize a package: declare data, assign default values, run the initialization section. What happens when an error occurs, causing the failure of this initialization process? It turns out that even if a package fails to complete its initialization steps, Oracle marks the package as having been initialized and does not attempt to run the startup code again during that session. To verify this behavior, consider the following package:

/* File on web: valerr.pkg */CREATE OR REPLACE PACKAGE valerrIS FUNCTION get RETURN VARCHAR2;END valerr;/CREATE OR REPLACE PACKAGE BODY valerrIS -- Note: this is a package-level, but private global variable v VARCHAR2(1) := 'ABC'; FUNCTION get RETURN VARCHAR2 IS BEGIN RETURN v; END;BEGIN DBMS_OUTPUT.PUT_LINE ('Before I show you v...'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Trapped the error!'); END valerr;

Suppose that I connect to SQL*Plus and try to run the valerr.get function (for the first time in that session). This is what I see:

SQL> exec DBMS_OUTPUT.PUT_LINE (valerr.get)*ERROR at line 1:ORA-06502: PL/SQL: numeric or value error: character string buffer too small

In other words, my attempt in the declaration of the v variable to assign a value of "ABC" caused a VALUE_ERROR exception. The exception section at the bottom of the package did not trap the error; it can only trap errors raised in the initialization section itself. And so the exception goes unhandled. Notice, however, that when I call that function a second time in my session, I do not get an error:

SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE ('V is set to ' || NVL (valerr.get, 'NULL')); 3 END; 4 /V is set to NULL

How curious! The statement "Before I show you v..." is never displayed; in fact, it is never executed. This packaged function fails the first time, but not the second or any subsequent times. Here we have one of those classic "unreproducible errors," and within the PL/SQL world, this is the classic cause of such a problem: a failure in package initialization.

These errors are very hard to track down. The best way to avoid such errors and also aid in detection is to move the assignments of default values to the initialization section, where the exception section can gracefully handle errors and report on their probable case, as shown here:

CREATE OR REPLACE PACKAGE BODY valerrIS v VARCHAR2(1); FUNCTION get RETURN VARCHAR2 IS BEGIN ... END;BEGIN v := 'ABC'; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Error initializing valerr:'); DBMS_OUTPUT.PUT_LINE (SQLERRM); END valerr;