ORA-04068: existing state of packages has been discarded

In this blog, we will see how to solve ORA-04068 with an example while using global variables.

Create a sample table:

CREATE TABLE MY_SAMPLE_TABLE(
COL1 NUMBER ,
COL2 VARCHAR2(50) )
/
Table MY_SAMPLE_TABLE created.

Now let us create a sample package that can be used as a type variable:


CREATE OR REPLACE PACKAGE pkg_test1 AS
 X NUMBER;
END;
/
Package PKG_TEST1 compiled


Create a simple trigger to insert data to my column no 1 for static values in my case there was additional logic but the issue was same.

create or replace trigger MY_SAMPLE_TABLE_TRG
before insert on MY_SAMPLE_TABLE
for each row
begin
   pkg_test1.X:=1;
     IF :NEW.COL1  IS NULL THEN
    :new.COL1 := pkg_test1.X;
  END IF;
end;
/
Trigger MY_SAMPLE_TABLE_TRG compiled

Now we see that the package type can be used globally in all other codes that is associated to a trigger.

For example if I want to use the same globla variable on any PLSQL block as shown below:

DECLARE
    l_n   NUMBER;
BEGIN
    pkg_test1.x   := 2;
    l_n           := pkg_test1.x;
    dbms_output.put_line(l_n);
END;

PL/SQL procedure successfully completed.
2

We see that it works fine. And status of all the above objects are valid as shown below:

Let us also try to insert a row to the table to verify if it works:

insert into MY_SAMPLE_TABLE values (null, 'Test 1');
/
1 row inserted.

Commit complete.

Now let us try to login with another session and try to modify the package and add a new variable,


So my old session is

Let us modify the object on my new session

CREATE OR REPLACE PACKAGE pkg_test1 AS
    x NUMBER;
    y VARCHAR2(10); -- new variable
END;
/
Package PKG_TEST1 compiled

Now let us go back to our old session and try to run the PLSQL block and also try to insert a row and check the results:

select sys_context('userenv','sid') from dual ;
/
SYS_CONTEXT('USERENV','SID')                                                                                                                                                                                                                                    
----------------------------------------------------------------------
464
/
DECLARE
    l_n   NUMBER;
BEGIN
    pkg_test1.x   := 2;
    l_n           := pkg_test1.x;
    dbms_output.put_line(l_n);
END;
/

Error report -
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SLOBA.PKG_TEST1" has been invalidated
ORA-04065: not executed, altered or dropped package "SLOBA.PKG_TEST1"
ORA-06508: PL/SQL: could not find program unit being called: "SLOBA.PKG_TEST1"
ORA-06512: at line 4
04068. 00000 -  "existing state of packages%s%s%s has been discarded"
*Cause:    One of errors 4060 - 4067 when attempt to execute a stored
           procedure.
*Action:   Try again after proper re-initialization of any application's
           state.

We see that using the package type on the PLSQL block we are getting the above errors, now let us give a try to insert a row.

insert into MY_SAMPLE_TABLE values (null, 'Test 2');
/
1 row inserted.

We know that PLSQL block is not working but while inserting it works fine.

Now let us go back to our second session and use a pragma (SERIALLY_REUSABLE) to the package and then try.

CREATE OR REPLACE PACKAGE pkg_test1 AS
 PRAGMA SERIALLY_REUSABLE ;
     X NUMBER;
     y varchar2(10); -- new variable
END;
/
Package PKG_TEST1 compiled

Now let us go back to our first session and try to run the PLSQL block and the insert too.

DECLARE
    l_n   NUMBER;
BEGIN
    pkg_test1.x   := 4;
    l_n           := pkg_test1.x;
    dbms_output.put_line(l_n);
END;
/
PL/SQL procedure successfully completed.
 

Now let us try the insert statement on our first session.

insert into MY_SAMPLE_TABLE values (null, 'Test 4 from session 1');
/
Error report -
ORA-06534: Cannot access Serially Reusable package "SLOBA.PKG_TEST1"
ORA-06512: at "SLOBA.MY_SAMPLE_TABLE_TRG", line 2
ORA-04088: error during execution of trigger 'SLOBA.MY_SAMPLE_TABLE_TRG'

We see that now we encountered another issue while using Serially Reusable on the package that used as TYPE.

To solve this we can add “PRAGMA AUTONOMOUS_TRANSACTION” and execute the insert and PLSQL block again.

CREATE OR REPLACE TRIGGER MY_SAMPLE_TABLE_trg BEFORE
    INSERT ON MY_SAMPLE_TABLE
    FOR EACH ROW
DECLARE
    PRAGMA autonomous_transaction;
BEGIN
    pkg_test1.x   := 1;
    IF
        :new.col1 IS NULL
    THEN
        :new.col1   := pkg_test1.x;
    END IF;

END;
/
Trigger MY_SAMPLE_TABLE_TRG compiled

Now let us try again:

insert into MY_SAMPLE_TABLE values (null, 'Test 4 from session 1');
 /
1 row inserted.

DECLARE
    l_n   NUMBER;
BEGIN
    pkg_test1.x   := 5;
    l_n           := pkg_test1.x;
    dbms_output.put_line(l_n);
END;
/
PL/SQL procedure successfully completed.

Then only part we need to keep in mind is “compound trigger” are not supported.

Thank you for reading my blog, please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.