I have encountered a weird behavior with initialization state of a package when ORA is thrown. Here is my example that I run on Oracle 12c:

create or replace package pkg is
   v_num number := pkg_two.getnum_two;
   function getnum return number;
end;

create or replace package pkg_two is
   v_num_two number := 1;
   
   function getnum_two return number;
end;

create or replace package body pkg is
   function getnum return number is
   begin
      return v_num;
   end;
end;

create or replace package body pkg_two is
   function getnum_two return number is
   begin
      return v_num_two;
   end;
begin
   dbms_lock.sleep(10);
end;

Now here is the weird behavior. I run the following code in SQLPlus and interrupt it after 5 seconds:

declare
   v_number number;
begin
   select pkg.getnum into v_number from dual;
   dbms_output.put_line('output: ('||v_number||')');
end;

This interrupting throws an ORA-01013. Then I run it again and after only a few milliseconds I get:

output: ()

This is weird, since in 12c the package should be set to initialized only once the initialization has been finished as I have found in this article: /

What is also weird is that if I replace the "SELECT INTO" with ":=" the behavior is as the article states:

declare
   v_number number;
begin
   v_number := pkg.getnum;
   dbms_output.put_line('output: ('||v_number||')');
end;

so after doing the same procedure of runnig it twice I get (after 10 seconds the second time):

output: (1)

Could someone please explain whether this behavior is expected? And if so, can it happen without using the "SELECT INTO" clause?

I have encountered a weird behavior with initialization state of a package when ORA is thrown. Here is my example that I run on Oracle 12c:

create or replace package pkg is
   v_num number := pkg_two.getnum_two;
   function getnum return number;
end;

create or replace package pkg_two is
   v_num_two number := 1;
   
   function getnum_two return number;
end;

create or replace package body pkg is
   function getnum return number is
   begin
      return v_num;
   end;
end;

create or replace package body pkg_two is
   function getnum_two return number is
   begin
      return v_num_two;
   end;
begin
   dbms_lock.sleep(10);
end;

Now here is the weird behavior. I run the following code in SQLPlus and interrupt it after 5 seconds:

declare
   v_number number;
begin
   select pkg.getnum into v_number from dual;
   dbms_output.put_line('output: ('||v_number||')');
end;

This interrupting throws an ORA-01013. Then I run it again and after only a few milliseconds I get:

output: ()

This is weird, since in 12c the package should be set to initialized only once the initialization has been finished as I have found in this article: https://rogertroller/2018/05/16/package-initialization-changes-between-oracle-11g-and-oracle-12c/

What is also weird is that if I replace the "SELECT INTO" with ":=" the behavior is as the article states:

declare
   v_number number;
begin
   v_number := pkg.getnum;
   dbms_output.put_line('output: ('||v_number||')');
end;

so after doing the same procedure of runnig it twice I get (after 10 seconds the second time):

output: (1)

Could someone please explain whether this behavior is expected? And if so, can it happen without using the "SELECT INTO" clause?

• plsql
• oracle12c

1 Answer 1

I don't see the behaviour you describe in 19c, so it has presumably been modified further since the changes in the article you referenced. It isn't clear if that was describing 12cR1 or 12R2, as it is tagged with both; but this similar article and LiveSQL demo from Steven Feuerstein specify it initially changed in 12cR1. It could have changed further between those two releases, or in 18c, or 19c, or some patch in between. You haven't said which version and patchset you see the difference in behaviour.

Anyway... the version doing:

v_number := pkg.getnum;

seems to be behaving as you expect, and matching what the article says. That version is entirely PL/SQL - you are calling the package function and assigning its return value directly to the PL/SQL variable, so there is no SQL involved.

The version doing:

select pkg.getnum into v_number from dual;

is behaving differently (for you, but not in 19c), but that has a context switch between PL/SQL and SQL. The select into... is essentially stepping out of PL/SQL to execute the SQL statement, and returning the results into the PL/SQL variable. (I would imagine you would see the same thing if you just did select pkg.getnum from dual directly in SQL, without the anonymous block?)

That suggests that the change to the initialization behaviour was initially handled differently. Whatever mechanism is being used to keep the package uninitialized (or uninstantiated), or perhaps revert it to that state, in your session when an exception occurs appears to only apply when that is seen in a PL/SQL context. That early iteration seems to have overlooked the same thing happening from a SQL context. And some later patch or version refined it to work the same way in both contexts. (Usually anyway - bug 34969407 indicates it's perhaps still not entirely consistent.) It would be interesting to know exactly when it changed.

This is necessarily speculation, partly because I don't have a 12c DB to test again, but also because I can't see any details about why it was changed or how it works internally - not that I would expect to - or any documentation changes in this area.

As it has been fixed, or at least refined, in later versions, I wouldn't say that the difference in behaviour is 'expected', in that it doesn't look like it was intentional. Given what you are seeing though, I would expect to see it in 12cR1 and probably some later versions/patchsets but then not in more recent ones. And even then probably only when there is a context switch involved.