Tags

, , ,

The package initialization block

When a session references a package item, the package is instantiated and initialized. Initializing means:

  • assigning values to public constants (constants defined in the package specification or body outside of packaged procedures and functions)
  • assigning values to public variables if values are specified in variable declaration
  • execute the initial block of the package body  
CREATE OR REPLACE PACKAGE test_init IS
   PROCEDURE show_values;
END test_init;
/

CREATE OR REPLACE PACKAGE BODY test_init IS
   g_before_error VARCHAR2(30) := 'NOT INITIALIZED';
   g_after_error  VARCHAR2(30) := 'NOT INITIALIZED';
   g_test         VARCHAR2(5);

   PROCEDURE show_values IS
   BEGIN
      sys.dbms_output.put_line('Before : ' || g_before_error);
      sys.dbms_output.put_line('After  : ' || g_after_error);
   END show_values;

-- INIT BLOCK
BEGIN
   g_before_error := 'INITIALIZED';
   g_test         := 'This will raise an error';
   g_after_error  := 'INITIALIZED';
END test_init;
/

What happens if the initialization block raises an error?

Now basically you get an error – as we would expect.

BEGIN
   test_init.show_values();
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TEST.TEST_INIT", line 13
ORA-06512: at line 2
06502. 00000 - "PL/SQL: numeric or value error%s"

So far so good – but now, a significant thing has changed between ORACLE 11 and ORACLE 12.

What happens if we call the show_values procedure again?

ORACLE 12 (same in 18)

BEGIN
   test_init.show_values();
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TEST.TEST_INIT", line 13
ORA-06512: at line 2
06502. 00000 - "PL/SQL: numeric or value error%s"

So in ORACLE 12 the initialization block is executed again. This indicates, that the package was not instantiated due to the error in the initialization block. 

ORACLE 11

BEGIN
   test_init.show_values();
END;
/

PL/SQL procedure successfully completed.

Before : INITIALIZED
After  : NOT INITIALIZED

In ORACLE 11 the packages has been instantiated even if the initialization run into an error. So a second call will not run the initialization again…

As you can see the second global variable (g_after_error) has not been changed by the initialization block whereas the first was.

 

What does this mean?

In my opinion the current (12c/18c) behaviour is much more consistent…but this change in behaviour can (and will) affect applications that did not care too much on whether the initialization run into an error in Oracle 11g – as a secondary call to a package item that does not depend on a correct initialization of the package worked..