Package Marked as Initialized Even if Initialization Fails - until 12.1!

View All Scripts Login to Run Script

Script Name Package Marked as Initialized Even if Initialization Fails - until 12.1!
Description When a session first references an element in a package, the PL/SQL runtime engine initializes the package: instantiates package-level data and assigns default values; executes the initialization section, if present. If assignment of a default value fails, the exception cannot be trapped "inside" the package. The exception section of the initialization section can only possibly trap errors raised within the initialization section. But the key thing to know is this: prior to 12.1, if package initialization fails, the package is still marked as initialized. So within that same session, the PL/SQL engine will not try to initialize again - which means you could be facing one of those awful situations: it fails the first time, but then runs. If you are experiencing this dynamic, disconnect and reconnect, and see if that causes the failure to appear again. Then you will know it is likely a failure in package initialization somewhere. In 12.1 and higher, when a package fails to initialize, it is marked as uninitialized.
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor Steven Feuerstein
Created Tuesday March 15, 2016

Statement 1

Can you tell by the name?

Hmmm What Will This Package Do?

CREATE OR REPLACE PACKAGE valerr 
IS 
   FUNCTION little_name RETURN VARCHAR2; 
END valerr;

Package created.

Statement 2

I declare a global variable and assign it a value. I also have an initialization section that runs each time the package is initialized in my session. Finally, an exception section with WHEN OTHERS to trap any error.

Analyze This Package Body

CREATE OR REPLACE PACKAGE BODY valerr 
IS 
   g_name   VARCHAR2 (1) := 'Lu'; 
 
   FUNCTION little_name RETURN VARCHAR2 
   IS 
   BEGIN 
      RETURN g_name; 
   END little_name; 
BEGIN 
   DBMS_OUTPUT.put_line ('Before I show you the name...'); 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      DBMS_OUTPUT.put_line ( 
         'Trapped the error: ' || DBMS_UTILITY.format_error_stack ()); 
END valerr;

Package Body created.

Statement 3

What will you see when you run this code?

First Reference to Package

BEGIN 
   DBMS_OUTPUT.PUT_LINE ('Name = ' || valerr.little_name); 
END;

ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SQL_XHITRQCLNGGVIMZMFNZOMNZRC.VALERR", line 3
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-06502

Statement 4
What will you see when you run this code again? To some extent, it depends. On LiveSQL, each step is a distinct server call (web apps are, almost always, stateless applications). So you will see the same failure as with the previousl step. If you download and run this script in SQL Developer, SQL*Plus, etc., then they will execute in the same session and so you should see "Name = " in this step.
Second Reference to Package
```
BEGIN 
   DBMS_OUTPUT.PUT_LINE ('Name = ' || valerr.little_name); 
END;
```
```
ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SQL_XHITRQCLNGGVIMZMFNZOMNZRC.VALERR", line 3
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1721
```
More Details: https://docs.oracle.com/error-help/db/ora-06502
Statement 5
Now I call the function, trap any error, then call it again. In this scenario, with a single server call, the package initialization process occurs - and fails. "Lu" is too big a name. But I trap the error, display the message and then continue - in the same session. So when I invoke valerr.little_name a second time, the package is marked as initialized - even though initialization failed. And so "Name = " is displayed.
Two Executions Within Same Block
```
BEGIN 
   BEGIN 
      DBMS_OUTPUT.PUT_LINE ('Name = ' || valerr.little_name); 
   EXCEPTION 
      WHEN OTHERS 
      THEN 
         DBMS_OUTPUT.PUT_LINE (SQLERRM); 
   END; 
 
   DBMS_OUTPUT.PUT_LINE ('Name = ' || valerr.little_name); 
END;
```
```
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Name = 
```

Statement 6

CREATE OR REPLACE PACKAGE valerr 
IS 
   FUNCTION little_name RETURN VARCHAR2; 
   FUNCTION big_name RETURN VARCHAR2; 
END valerr;

Package created.

Statement 7

CREATE OR REPLACE PACKAGE BODY valerr 
IS 
   g_big_name VARCHAR2 (100) := 'LuAnn the Champion Wrestler'; 
   g_name     VARCHAR2 (1) := 'Lu'; 
 
   FUNCTION little_name RETURN VARCHAR2 
   IS 
   BEGIN 
      RETURN g_name; 
   END little_name; 
 
   FUNCTION big_name RETURN VARCHAR2 
   IS 
   BEGIN 
      RETURN g_big_name; 
   END big_name; 
BEGIN 
   DBMS_OUTPUT.put_line ('Before I show you the name...'); 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      DBMS_OUTPUT.put_line ( 
         'Trapped the error: ' || DBMS_UTILITY.format_error_stack ()); 
END valerr;

Package Body created.

Statement 8

BEGIN  
   DBMS_OUTPUT.PUT_LINE ('Big name = ' || valerr.big_name);  
END;

ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SQL_XHITRQCLNGGVIMZMFNZOMNZRC.VALERR", line 4
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-06502

Statement 9

BEGIN  
   DBMS_OUTPUT.PUT_LINE ('Big name = ' || valerr.big_name);  
END;

ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SQL_XHITRQCLNGGVIMZMFNZOMNZRC.VALERR", line 4
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-06502

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database