What is below is taken from an actual analysis of heavy latching and invalid objects after a change was made. This was done in early 2011.
For a long period of time, we have avoided making changes that would result in database object “invalidation”. I was always curious as to why this was, as oracle supports dynamic recompilation of invalid objects. In other words, if an invalid object were to be encountered at execution time, the oracle software would place the object back into an executable state after checking that everything was in place (tables and grants existed, objects on which the invalid object depended were in fact in a valid state themselves, etc.
However, while running a test on Friday, I found that when a given database package (XWC _DATA_LOADER) was rendered invalid by a change to a table which it used in its source code, it was in fact not recompiled at the next execution. This in turn causes inserts via the XWC_ONLINE_DATA_LOADER package to fail “silently” (although logged in XWC_ERRORS). As a matter of fact, even a manual recompilation of the package was ignored by existing, ongoing sessions that had been successfully executing procedures in the package. This was new to me, and I couldn’t explain why it was doing this. This is what normally (for good reason) requires us to either do 2AM maintenance, avoid changes like the bubonic plague, or some combination of both.
However, I finally discovered why we have this issue and wanted to share the information. This explains a lot of outages we have had over the years when objects are rendered invalid.
It is due exclusively to how we write our PL/SQL exception handlers.
We write a when others then in most cases, and this subverts the automatic recompilation. The exception handler itself is good, as it catches and at a minimum logs any exceptions we haven’t specifically handled, but doesn’t re-raise them. As noted, this subverts the automatic recompilation.
To fix this, we can simply add a raise for the when others exception handlers we have. The application would then handle the exception, perhaps abnormally, but the session would be able to continue after it recompiled the package.
Below is a simple example that shows this.
As-Is test
We start with a simple table with the following structure, and make sure we have no data.
SQL> delete from t0121
2 /
1 row deleted.
We then create two simple PL/SQL packages to handle the table. Actually, only one of the packages (B) accesses the table, while the other (A) calls the “handler” package.
SQL> create or replace package a is
2 l number := 10;
3 procedure run_b;
4 end a;
5 /
Package created.
SQL> create or replace package body a is
2 procedure run_b is
3 begin
4 b.procb;
5 exception when others then
6 dbms_output.put_line(sqlerrm);
7 end;
8 end a;
9 /
Package body created.
SQL> create or replace package b is
2 l number := 10;
3 procedure procb;
4 end b;
5 /
Package created.
SQL> create or replace package body b is
2 procedure procb is
3 begin
4 insert into t0121 values(1);
5 exception when others then
6 dbms_output.put_line(sqlerrm);
7 end;
8 end b;
9 /
Package body created.
We then run a simple PL/SQL routine that ensures we drop a column, which invalidates our procb procedure in our B package.
SQL> declare
2 l number;
3 begin
4 select count(*) into l from user_tab_columns where table_name = 'T0121' and column_name = 'D';
5 if l = 1 then
6 execute immediate 'alter table t0121 drop column d';
7 else
8 execute immediate 'alter table t0121 add d number';
9 execute immediate 'alter table t0121 drop column d';
10 end if;
11 end;
12 /
PL/SQL procedure successfully completed.
We are now ready to run our test.
To begin, we show we have no data in the table.
SQL> select * from t0121;
no rows selected
We then show that our package body for the B package is invalid. A is not invalid, as the package specification for B is not invalid. This is what we normally see in production.
SQL> select object_name,object_type,status from user_objects where object_name in ('A','B');
OBJECT_NAME OBJECT_TYPE STATUS
--------------- -------------------- ---------------
B PACKAGE BODY INVALID
B PACKAGE VALID
A PACKAGE BODY VALID
A PACKAGE VALID
When we run the run_b procedure in our A package, we see that the error message is printed out screen, as we have requested the program do. In our case, we write the error to the XWC_ERRORS table.
SQL> exec a.run_b
ORA-06508: PL/SQL: could not find program unit being called
PL/SQL procedure successfully completed.
We also see that our B package body is still invalid.
SQL> select object_name,object_type,status from user_objects where object_name in ('A','B');
OBJECT_NAME OBJECT_TYPE STATUS
--------------- -------------------- ---------------
B PACKAGE BODY INVALID
B PACKAGE VALID
A PACKAGE BODY VALID
A PACKAGE VALID
We again execute our run_b procedure in A, and see that we have the same exception printed to the screen. We also see the B package body is still invalid.
SQL> exec a.run_b
ORA-06508: PL/SQL: could not find program unit being called
PL/SQL procedure successfully completed.
SQL> select object_name,object_type,status from user_objects where object_name in ('A','B');
OBJECT_NAME OBJECT_TYPE STATUS
--------------- -------------------- ---------------
B PACKAGE BODY INVALID
B PACKAGE VALID
A PACKAGE BODY VALID
A PACKAGE VALID
Finally, we show that we have no data in our table, as none of inserts worked since B is invalid.
SQL> select * from t0121;
no rows selected
SQL>
This is where we stand today.
However, if we simply add a raise if the exception is an ORA-6508 (PL/SQL: could not find program unit being called), this will force the standard recompilation by Oracle to occur.
To-Be test
See below for the same test as above, only we add a raise if the SQL error code = -6508.
The only changes we show are the package body definitions, as those are what we change.
SQL> create or replace package body a is
2 procedure run_b is
3 begin
4 b.procb;
5 exception when others then
6 if sqlcode = -6508 then
7 raise;
8 else
9 dbms_output.put_line(sqlcode);
10 end if;
11 end;
12 end a;
13 /
Package body created.
SQL> create or replace package body b is
2 procedure procb is
3 begin
4 insert into t0121 values(1);
5 exception when others then
6 if sqlcode = -6508 then
7 raise;
8 else
9 dbms_output.put_line(sqlcode);
10 end if;
11 end;
12 end b;
13 /
Package body created.
We then run our routine that changes our table and invalidates our package again, which we also show.
SQL> declare
2 l number;
3 begin
4 select count(*) into l from user_tab_columns where table_name = 'T0121' and column_name = 'D';
5 if l = 1 then
6 execute immediate 'alter table t0121 drop column d';
7 else
8 execute immediate 'alter table t0121 add d number';
9 execute immediate 'alter table t0121 drop column d';
10 end if;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> select object_name,object_type,status from user_objects where object_name in ('A','B');
OBJECT_NAME OBJECT_TYPE STATUS
--------------- -------------------- ---------------
B PACKAGE BODY INVALID
B PACKAGE VALID
A PACKAGE BODY VALID
A PACKAGE VALID
We then run our package, and we now have an “unhandled” exception by virtue of the fact we asked Oracle to raise the exception if it occurred.
SQL> exec a.run_b
BEGIN a.run_b; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "REP.B" has been invalidated
ORA-04065: not executed, altered or dropped package body "REP.B"
ORA-06508: PL/SQL: could not find program unit being called: "REP.B"
ORA-06512: at "REP.A", line 7
ORA-06512: at line 1
We also see our package is currently invalid.
SQL> select object_name,object_type,status from user_objects where object_name in ('A','B');
OBJECT_NAME OBJECT_TYPE STATUS
--------------- -------------------- ---------------
B PACKAGE BODY INVALID
B PACKAGE VALID
A PACKAGE BODY VALID
A PACKAGE VALID
However, the next time we execute our A package run_b procedure, it executes successfully…
SQL> exec a.run_b
PL/SQL procedure successfully completed.
…and we show it has been recompiled internally by Oracle…
SQL> select object_name,object_type,status from user_objects where object_name in ('A','B');
OBJECT_NAME OBJECT_TYPE STATUS
--------------- -------------------- ---------------
B PACKAGE BODY VALID
B PACKAGE VALID
A PACKAGE BODY VALID
A PACKAGE VALID
…and we have data from the execution above.
SQL> select * from t0121;
C
----------
1
SQL>
Below shows the document on My Oracle Support (MOS) that shows this.
DOC ID 106206.1
As background, when the ORA-4068 is raised, ORACLE will throw away all existing instantiations of the package. When the package (more properly, the subprogram referring to the package) is re-executed, ORACLE will re-instantiate the package automatically (if possible), which typically will succeed, and re-execution of the subprogram will succeed.
An important proviso is that the ORA-4068 error must be unhandled on exit from the subprogram in order for this solution to work. It’s only when an _unhandled_ ORA-4068 is returned by PL/SQL to ORACLE that the needed deinstantiations take place. (The ORA-4068 may be handled in the subprogram and various actions taken in the handler, but the error must be reraised in order for it to be unhandled on exit from PL/SQL to get the desired deinstantiations.)
2 comments for “Gotcha on automatic PL/SQL recompilation”