Hi!
I would like to ask for some explanations concerning the implicit reinitialization of statefull package when it is encapsulated in exception handling structure.
Let me share the statements via livesql:
https://livesql.oracle.com/apex/livesql/s/cxh8bv4sbf4o32rahsea95aau The core statements are 7, 8, and 9. Statements 7 and 8 work as I expect (package is reinitialized after the first exception and then works fine). The problem is with Statement 9 where exception handling with dbms_output is added - in this case package is NOT reinitialized at the second call. This is what I don't understand.
Statements 1 to 4 are used for setting up the 'environment' to reproduce the problem. What I try to do:
1. invalidate the VT view (thus package PKG_STATE also) be execution Statement 5. As PKG_STATE is statefull - it should be reinitialized at the next call
2. call Statement 7 or 8 or 9 -> will produce ORA-04068/ORA-04061/ORA-04065/ORA-06508 messages
3. repair the VT view (thus PKG_STATE) - Statement 6
4. call again Step 2. In case of Statement 9 - package is not reinitialized and this simple script always returns the same customized error message
I hope this description is clear enough for you.
I'm looking forward to any advice or hint from you since I'm very curious what I do wrong in Statement 9.
Kindly regards,
Andrzej
[UPDATE] It seems that my description wasn't clear enough :) Sorry for that - I'm new to LiveSQL.
Please find below three complete testcases, the first two work as expected, the third - no. During third execution of pkg_state.print I'm got also ORA-06508 instead of expected "i = 1":
testcase#1 (OK):
https://livesql.oracle.com/apex/livesql/s/cxjt5u5h6eavkfvpn8ca4tu01 testcase#2 (OK):
https://livesql.oracle.com/apex/livesql/s/cxjt5u5h960ghmco63zepy1at testcase#3 (NOK):
https://livesql.oracle.com/apex/livesql/s/cxjvyzdg305ikg5e5c8i6idli
Thanks for providing working test cases and using LiveSQL!
The issue here is the test 3 doesn't raise the ORA-4068 back to the client. This is necessary to reset the package state. As stated in MOS note 1161225.1:
Subsequent attempts to call the package will either succeed if the package was successfully recompiled, or raise the underlying error if it was not. Error messages further down the error stack from the ORA_4068 indicate what prompted the recompilation to occur and may indicate whether a subsequent call will fail. An important proviso is that the ORA-4068 must be unhandled on exit from the subprogram in order for this solution to work. It is only when an unhandled ORA-4068 is returned by PL/SQL to ORACLE that the needed deinstantiations take place
It's only when you leave ORA-4068 unhandled that Oracle resets the package state and validates the package. You can see this by checking the status in user_objects. When you handle the exception it remains INVALID:
SQL> /*
SQL> After initial ORA-04068/ORA-04061/ORA-04065/ORA-06508, package seems to be not reinitialized successfully. Each of the following executions still prints 'E
RROR: ORA-06508: PL/SQL: could not find program unit being called'.
SQL> */
SQL> begin
2 pkg_state.print;
3 exception
4 when others then
5 DBMS_OUTPUT.PUT_LINE('ERROR: '||SQLERRM);
6 end;
7 /
ERROR: ORA-06508: PL/SQL: could not find program unit being called
SQL>
SQL> select object_type, status from user_objects
2 where object_name = 'PKG_STATE';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY INVALID
SQL>
SQL> begin
2 pkg_state.print;
3 exception
4 when others then
5 DBMS_OUTPUT.PUT_LINE('ERROR: '||SQLERRM);
6 end;
7 /
ERROR: ORA-06508: PL/SQL: could not find program unit being called
SQL>
SQL> select object_type, status from user_objects
2 where object_name = 'PKG_STATE';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY INVALIDWhen you leave it unhandled or re-raise it, then the package state is reset:
SQL> alter table tt rename to t;
SQL>
SQL> select object_type, status from user_objects
2 where object_name = 'PKG_STATE';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY INVALID
SQL>
SQL> PRO reinitialization; prints: i=1
reinitialization; prints: i=1
SQL> PRO WORKS AS EXPECTED
WORKS AS EXPECTED
SQL> begin
2 pkg_state.print;
3 end;
4 /
SQL>
SQL> select object_type, status from user_objects
2 where object_name = 'PKG_STATE';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY VALID
For further explanation see MOS notes 783675.1 & 1161225.1