Skip to main content

What happens when a package fails to initialize? New behavior as of 12.1!

The best way to build applications on top of Oracle Database is to build lots of APIs (application programmatic interfaces) to your data with PL/SQL packages.

And that means you end up with lots of packages in your application. That's just great!

Now, when a user selects a feature of your application that in turn references an element in a package (invokes a procedure or function, or reads the value of a constant), that package must be instantiated and initialized for that user's session. As described in the documentation:
When a session references a package item, Oracle Database instantiates the package for that session. Every session that references a package has its own instantiation of that package. 
When Oracle Database instantiates a package, it initializes it. Initialization includes whichever of the following are applicable:
  • Assigning initial values to public constants
  • Assigning initial values to public variables whose declarations specify them
  • Executing the initialization part of the package body
Ah, but what happens when any of these steps fail? That, dear reader, is the focus of this post.

Suppose I have a procedure that raises an exception when executed:

CREATE OR REPLACE PROCEDURE always_fails
IS
BEGIN
   RAISE PROGRAM_ERROR;
END;
/

Then no matter how many times I try to run this procedure, it terminates with that same exception:

BEGIN
   always_fails;
END;
/

ORA-06501: PL/SQL: program error
ORA-06512: at "STEVEN.ALWAYS_FAILS", line 4

BEGIN
   always_fails;
END;
/

ORA-06501: PL/SQL: program error
ORA-06512: at "STEVEN.ALWAYS_FAILS", line 4

At which point you must now be saying: "Well, duh, Steven. Of course you are going to see the same exception each time you try to run the procedure."

Exactly. Just so. OK, now let's try it again, with the following package. When the package is initialized, it assigns (or tries to assign) a value of "Lu" to g_name. But that assignment fails, since "Lu" is too big to fit into a VARCHAR2(1) variable.

Thus, the PL/SQL engine raises the VALUE_ERROR exception (ORA-06502).

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

Package compiled

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 compiled


So what happens when I try to execute the little_name function, after compiling the package?

I see an unhandled exception:

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 "VALERR", line 3

Before going any further, let's make sure you understand why the exception went unhandled. After all, the package body has a "catch-all" exception handler:

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         'Trapped the error: ' || DBMS_UTILITY.format_error_stack ());
END valerr;

So why did the exception go unhandled? Because the error occurred in the "declaration section" of the package (not within the initialization section of the package or the executable section of a subprogram of the package). Exception sections only handle errors raised in the executable section of code (see my video for more details).

OK, so now we know:
  1. The package failed to finish initializing.
  2. An exception raised when assigning a default value to a package-level variable or constant cannot be handled within the package.
So if I try to execute this function again, I will see the same error, right? Well, maybe - depending on your version of Oracle Database. Prior to 12.1, you will see this:

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

Name = 

No exception. Instead, the valerr.little_name function returns a NULL value. Huh?

Yes, I know. That seems counter-intuitive, but here's the thing: prior to Oracle Database 12c Release 1, even if a package failed to initialize, it would be marked as initialized in that session. And any variables or constants that had already successfully been assigned a value would have those values. Which can make it tough to track down the error.

But as of 12.1, when a package fails to initialize, then that package is marked as uninitialized. And any subsequent effort to use that package will grow the same exception. So in 12.1 (and 12.2 and 18.1 and....) you will see:

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 line 2

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 line 2

Conclusion

I expect you will all agree that the 12.1 behavior is preferred to the earlier "Oh, that package has a problem? Not to worry!" approach.

And ideally this change would not result in changed behavior for your application.

As in: hopefully, your testing is good enough so that you would have noticed a package initialization failure.

Finally, if you'd like to test your knowledge on this topic, try our Oracle Dev Gym quiz.

Comments

  1. How totally refreshing it is to read this specific blog article Stephen.

    I have documented this (10 and 11g) behaviour previously here for example

    https://www.freelists.org/post/oracle-l/Oracle-1011g-INVALID-objects-issue

    The first invocation of

    EXEC test_pkg.doStuff0 executes

    in my sample code raises the expected error. The second and subsequent times it does not.

    You have not used the words 'Breaking Change' so I will. In 12c Oracle have implemented a Breaking Change. It is for the better too.

    Mike
    http://www.strychnine.co.uk

    ReplyDelete
  2. Mike, just one comment on your blog post. You say: "I cannot rely on
    ALL_OBJECTS.STATUS to determine whether a database package is an invalid
    state or not." That is not true. The package is perfectly valid from a compilation standpoint, which is all that ALL_OBJECTS.STATUS reflects. It fails at runtime.

    Also, as of 12.1, if you turn on compile time warnings, you will notice that those assignments of literals will be flagged with:

    PLW-06017: an operation will raise an exception

    You can set this warning to be treated as an error, and THEN the package status *will* be set to INVALID.

    ReplyDelete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel