A nasty problem has been haunting me for a while now in my codebase: Every time I run my full utPLSQL test suite, one specific test-package fails every test with ORA-04061 - Existing state of Package has been invalidated. But when I run this specific test package, everything works fine.
Today I followed the rabbit hole and to get a better understanding of what’s going on I tried to extract what I learned into a simple example in my favourite universe.
The situation
Let’s assume we have a table of popular Sith and instead of allowing direct manipulation of the table with have a package sith_manager that handles adding new popular Sith.
create table popular_sith (
id integer primary key not null,
name varchar2(200)
);
create sequence popular_sith_seq start with 1;
create or replace package sith_manager as
procedure add_popular_sith( i_name varchar2 );
end;
/
create or replace package body sith_manager as
/* To better reveal our intention and to keep
things that might change separated from the
implementation, we use a constant for the default
sith name
*/
gc_default_name constant varchar2(200) := 'Darth'||' Ora';
procedure add_popular_sith( i_name varchar2 )
as
begin
insert into popular_sith (id, name )
values (
popular_sith_seq.nextval,
nvl(i_name, gc_default_name)
);
end;
end;
/
To validate that in case of passing NULL the default Sith-Name is used, we create a Unit-Test.
There is one difficulty, though: We don’t know what ID our test-entry will get because the add_popular_sith-function uses an underlying sequence. For name is not unique, how can we know which entry to test?
(For some folks pointed it out: I am very aware that this solution is not well-testable and that there are several better ways to deal with the problem. This example only exists to superficially create the scenario I want to demonstrate.
There will be a follow-up blog post that shows alternative, better solutions to solve this problem.)
So let’s control the sequence and make sure it will give a specific ID when we run our test.
For Unit-Tests should only be installed on dev and test environments, that’s not as problematic as it would be in a production environment. However, there are better ways to deal with this kind of situation which I will cover in a future blog post.
create or replace package ut_sith_manager as
-- %suite(Sith Manager)
-- %suitepath(darkSide.management)
procedure control_sequence;
-- %test(ADD_POPULAR_SITH uses default name on NULL param)
-- %beforetest(control_sequence)
procedure add_sith_default_on_null_param;
-- %afterall
procedure reset_sequence;
end;
/
create or replace package body ut_sith_manager as
/* To control which ID our test-entry will be get,
we control the underlying sequence
*/
procedure control_sequence as
pragma autonomous_transaction;
begin
/* I am aware that this is not a good way to reset
sequences. But the whole purpose of this example
*is* to invalidate the dependent object.
For a better approach google
"oracle reset sequence tom kyte"
*/
execute immediate 'drop sequence popular_sith_seq';
execute immediate 'create sequence popular_sith_seq' ||
' minvalue -100 start with -100';
end;
/* After the test is done, we want to set the
sequence back to its highest value
*/
procedure reset_sequence as
pragma autonomous_transaction;
l_max_id integer;
begin
select nvl(max(id),0)+1 into l_max_id from popular_sith;
execute immediate 'drop sequence popular_sith_seq';
execute immediate 'create sequence popular_sith_seq'||
' start with '||l_max_id;
end;
procedure add_sith_default_on_null_param
as
l_actual_row popular_sith%rowtype;
begin
-- Act
sith_manager.add_popular_sith(null);
-- Assert
select * into l_actual_row
from popular_sith
where id = -100;
ut.expect(l_actual_row.name).to_equal('Darth Ora');
end;
end;
/
> utplsql run user/pw@localhost -cq -p="ut_sith_manager"
darkside
management
Sith Manager
ADD_POPULAR_SITH uses default name on NULL param [,045 sec]
Finished in ,061706 seconds
1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
So far, so good. Everything works fine.
The Problem
However, if we query for invalid objects after running the test, we will see the following entry:
select
object_name,
status,
last_ddl_time
from user_objects
where status <> 'VALID';
| OBJECT_NAME | STATUS | LAST_DDL_TIME |
|---|---|---|
| SITH_MANAGER | INVALID | 2020-04-08 20:53:19 |
Our sith_manager package has been invalidated because of the drop and recreation of the sequence it is referencing. Not a big problem, though, because it’s automatically recompiled when we use it the next time.
Let’s add a sith_generator to create a lot of popular sith then:
create or replace package sith_generator as
procedure generate_popular_sith(
i_name varchar2,
i_count integer
);
end;
/
create or replace package body sith_generator as
procedure generate_popular_sith(
i_name varchar2,
i_count integer
) as
begin
if nvl(i_count,0) > 0 then
for i in 1..i_count loop
sith_manager.add_popular_sith(
i_name || ' ' || i
);
end loop;
end if;
end;
end;
/
Again, we can not check the output directly, but at least we can control the input name and therefore write our test accordingly:
create or replace package ut_sith_generator as
-- %suite(Sith Generator)
-- %suitepath(darkSide)
-- %test(GENERATE_POPULAR_SITH creates correct number of Sith with name "<GIVEN_NAME> <COUNTER>")
procedure generate_popular_sith;
end;
/
create or replace package body ut_sith_generator as
procedure generate_popular_sith as
c_actual sys_refcursor;
c_expect sys_refcursor;
begin
-- Act
sith_generator.generate_popular_sith('MyTest_Popular_Sith', 3);
-- Assert
open c_actual for
select name from popular_sith
where name like 'MyTest_Popular_Sith%'
order by name;
open c_expect for
select column_value name
from table(sys.odcivarchar2list(
'MyTest_Popular_Sith 1',
'MyTest_Popular_Sith 2',
'MyTest_Popular_Sith 3'
));
ut.expect(c_actual).to_equal(c_expect);
end;
end;
/
> utplsql run user/pw@localhost -cq -p="ut_sith_generator"
darkside
Sith Generator
GENERATE_POPULAR_SITH creates correct number of Sith with name "<GIVEN_NAME> <COUNTER>" [,018 sec]
Finished in ,021618 seconds
1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
But what happens when we call both our tests in one run, e.g. by their shared Suitepath darkside?
> utplsql run user/pw@localhost -cq -p=":darkside"
darkside
Sith A Generator
GENERATE_POPULAR_SITH creates correct number of Sith with name "<GIVEN_NAME> <COUNTER>" [,026 sec]
management
Sith-Manager
ADD_POPULAR_SITH uses default name on NULL param [,009 sec] (FAILED - 1)
Failures:
1) add_sith_default_on_null_param
ORA-04061: Existing state of package body "SITHDB.SITH_MANAGER" has been invalidated
ORA-04065: not executed, altered or dropped package body "SITHDB.SITH_MANAGER"
ORA-06508: PL/SQL: could not find program unit being called: "SITHDB.SITH_MANAGER"
ORA-06512: in "SITHDB.UT_SITH_MANAGER", line 36
ORA-06512: in "SITHDB.UT_SITH_MANAGER", line 36
ORA-06512: in line 6
Finished in ,047853 seconds
2 tests, 0 failed, 1 errored, 0 disabled, 0 warning(s)
Oh my Lord Sith – we are in a situation where both of our packages run just fine on their own, but when sith_generator is run before sith_manager in the same session, we have failing tests – one of the worst circumstances to be in (especially when your whole suite of tests needs some time to run).
But what happens? What is the problem?
There are two things in combination that cause the error:
1. sith_manager contains so-called PL/SQL package state, because it uses a global package variable: gc_default_name. The constant keyword does not prevent the PL/SQL compiler from treating the variable as global state as soon as there is any kind of computation (in our case concatenating 2 strings).
2. sith_manager is invalidated by our ut_sith_manager unit-test.
ut_sith_generator first calls sith_generator which calls and initializes sith_manager. At this point, sith_manager has active global state.
Second, ut_sith_manager invalidates sith_manager causing all active global state to be reset.
For the compiler can not know whether that’s problematic or not, it will cause the ORA-04061 to inform that there has been state that’s now gone.
The Solution
For this problem is caused by the combination of two things, we can divide possible solutions into two categories:
- Avoid package to have global state
- Avoid invalidation of package
In this blog post, I will concentrate on the elimination of global state. There will be a follow-up post that dives into the possibilities to prevent the packages to be invalidated.
Option 1: Use Constant Values that are recognized by the Compiler
If you are on Oracle 12.2 or higher, it will be enough to change the value of our constant to eliminate our global state:
create or replace package body sith_manager as
gc_default_name constant varchar2(200) := 'Darth Ora';
procedure add_popular_sith( i_name varchar2 )
as
begin
insert into popular_sith (id, name )
values (
popular_sith_seq.nextval,
nvl(i_name, gc_default_name)
);
end;
end;
/
The PL/SQL compiler might be able to correctly recognize constants as such – as long as you don’t do any kind of computation.
Option 2: PRAGMA SERIALLY_REUSABLE
By adding pragma serially_reusable to the sith_manager package, we can also avoid global state to be created:
create or replace package sith_manager as
pragma serially_reusable;
procedure add_popular_sith( i_name varchar2 );
end;
/
create or replace package body sith_manager as
pragma serially_reusable;
gc_default_name constant varchar2(200) := 'Darth'||' Ora';
procedure add_popular_sith( i_name varchar2 )
as
begin
insert into popular_sith (id, name )
values (
popular_sith_seq.nextval,
nvl(i_name, gc_default_name)
);
end;
end;
/
What happens is that any global package state that might be created is immediately deleted after each call.
The problem is, that this renders the whole package impossible to be called from SQL:
create or replace package sith_manager as
pragma serially_reusable;
procedure add_popular_sith( i_name varchar2 );
function id_by_name( i_name varchar2 )
return varchar2;
end;
/
create or replace package body sith_manager as
pragma serially_reusable;
gc_default_name constant varchar2(200) := 'Darth'||' Ora';
procedure add_popular_sith( i_name varchar2 )
as
begin
insert into popular_sith (id, name )
values (
popular_sith_seq.nextval,
nvl(i_name, gc_default_name)
);
end;
function id_by_name( i_name varchar2 )
return varchar2
as
l_result integer;
begin
select id into l_result
from popular_sith
where name = i_name;
return l_result;
exception when no_data_found then
return null;
end;
end;
/
call sith_manager.add_popular_sith('Darth Vader');
select sith_manager.id_by_name('Darth Vader') from dual;
The last select will cause ORA-06534: Cannot access Serially Reusable package "SITHDB.SITH_MANAGER".
There are also other limitations, for example it’s not possible to have pipelined table functions in such a package.
Option 3: Move Constants to a separate Package
This is not exactly “avoiding global state”, but avoiding global state in the package that will become invalidated:
By moving the constants to a separate package we make sure that any invalidation of the package that holds the implementation has no impact on the configuration stuff we hold in global state.
create or replace package sith_manager_const as
gc_default_name constant varchar2(200) := 'Darth'||' Ora';
end;
/
create or replace package sith_manager as
procedure add_popular_sith( i_name varchar2 );
end;
/
create or replace package body sith_manager as
procedure add_popular_sith( i_name varchar2 )
as
begin
insert into popular_sith (id, name )
values (
popular_sith_seq.nextval,
nvl(i_name, sith_manager_const.gc_default_name)
);
end;
end;
/
While it might be inconvenient to be forced to have these constants now public, it prevents the ORA-04061 very effectively.
There can also be benefits to have shared public constants, but be mindful when introducing more coupling.
Option 4: Use Functions
Another option is to replace the package variable with a function that returns the fixed value:
create or replace package sith_manager as
function gc_default_name
return varchar2 deterministic;
procedure add_popular_sith( i_name varchar2 );
end;
/
create or replace package body sith_manager as
function gc_default_name
return varchar2 deterministic as
begin
return 'Darth '||'Ora';
end;
procedure add_popular_sith( i_name varchar2 )
as
begin
insert into popular_sith (id, name )
values (
popular_sith_seq.nextval,
nvl(i_name, gc_default_name)
);
end;
end;
/
To use the function in SQL (even in SQL used inside the Package), we need to make it public. Alternatively we could store the function result in a local variable first, in that case we wouldn’t need to make the function public.
I am also concerned that this kind of use might obfuscate the intention of the code. Make sure to introduce strong naming conventions among your team to make it clear when a function’s only purpose is to serve as kind of constant.
(Not An) Option 5: DBMS_SESSION.RESET_PACKAGE
It might be tempting to think that using dbms_session.reset_package will solve our global state problem.
This procedure resets all package state and we can even force to reinitialize it:
dbms_session.reset_package();
dbms_session.modify_package_state( dbms_session.reinitialize );
But for utPLSQL stores a lot of temporary information in its internal package state, this will inevitably lead to an empty test output.
Conclusion
Do you have more ideas how to deal with ORA-04061 in such a setting? Then reach out to me – or wait for my follow-up post on the “Avoid invalidation of the package” options 😉
As always you can find a full example on my github repository.
Update 2020-04-08: Added disclaimer about the example purposefully not being well-testable.
Update 2020-04-08: Added Option 4: Use Functions.
Update 2020-04-08: Added (Not An) Option 5: DBMS_SESSION.RESET_PACKAGE
0 Comments