ORA-04068: existing state of packages string has been discarded

I got ” ORA-04068: existing state of packages string has been discarded ”  error in Oracle database.

 

ORA-04068: existing state of packages string has been discarded

 

Details of error are as follows.

ORA-04068: existing state of packages string 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.
    

 

 

existing state of packages string has been discarded

This ORA-04068 errors are related with the One of errors 4060 – 4067 when attempt to execute a stored procedure.

Try again after proper re-initialization of any application’s state.

Recreating a table and then trying to execute a procedure dependent on the altered table in the same pl/sql block fails with ORA-04068

 

1) Create the tables and procedure

create table kdummy (vstat varchar2(2000));
   
create table kdrop (eno number);
   
insert into kdrop values(10);

create or replace procedure ktest as
    vcnt number;
begin
   dbms_output.put_line (' Hi in ktest ');
     select count(*) into vcnt from kdrop;
   dbms_output.put_line (' Count ' || vcnt);
end;
/


2) Pl/sql block

declare
     v_cnt number;
     v_stat varchar2(20);
     sql_stmt varchar2(200);
     ds_error   varchar2(2000);
Begin
     delete from kdummy;
      commit;
     select status into v_stat from user_objects where OBJECT_NAME = 'KTEST';
      insert into kdummy values('1 status of KTEST is ' || v_stat);
    SELECT COUNT(*) INTO v_cnt FROM USER_TABLES WHERE TABLE_NAME = 'KDROP';
    
    IF v_cnt > 0 THEN
     sql_stmt:= 'DROP TABLE KDROP';
     EXECUTE IMMEDIATE sql_stmt;
    END IF;
    select status into v_stat from user_objects where OBJECT_NAME = 'KTEST';
    insert into kdummy values('2 status of KTEST after DROP is ' || v_stat);
    
    sql_stmt:= 'create table kdrop (eno number)';
        EXECUTE IMMEDIATE sql_stmt;
    select status into v_stat from user_objects where OBJECT_NAME = 'KTEST';
 insert into kdummy values('3 status of KTEST after recreate is ' || v_stat);
    
    EXECUTE IMMEDIATE 'ALTER PROCEDURE KTEST COMPILE';
    select status into v_stat from user_objects where OBJECT_NAME = 'KTEST';
 insert into kdummy values('4 status of KTEST after recompile is ' || v_stat);
    
    insert into kdummy values('5 Call the procedure ');
    commit;
    ktest;
End;
/


3) Error

ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "<USER>.KTEST"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 34

Note:- Added dbms_session.reset_package; but still the same error

4) Debbugging statements  inserted into kdummy table

1 status of KTEST is VALID
2 status of KTEST after DROP is INVALID
3 status of KTEST after recreate is INVALID
4 status of KTEST after recompile is VALID
5 Call the procedure

The basic model of PL/SQL compilation relies on establishing certain facts at compile time that can
then be relied on at run time without the expense of re-checking the facts then. The dependency tracking subsystem must, and does, guarantee the safety of this. In other words, when a referenced object
is recompiled (or altered) then all its dependants are invalidated[1].

PL/SQL uses an on-demand linking model: only when the point of execution reaches the first invocation
that needs a particular unit is that unit instantiated in the session. Once such a called unit is instantiated,
then it cannot be recompiled when the point of execution is in, or has passed though, the unit and the
current server call is still active. A locking mechanism enforces this. If you modify your test to invoke the procedure before you try to recompile it, then you’ll get an indefinite hang. The more common case is
that a different session tries to compile the unit. Then it just has to wait until the first session’s server
call is no longer active.

It’s easier to discuss this with two procedures: “Parent” that does something simple like announce
its name; and “Dependant” that statically invokes “Parent” as its last statement. (See below.)

If, after the moment that the point of execution enters “Dependant”, but before it reaches the invocation
of “Parent”, an attempt to recompile “Parent” it made, will not be locked out. As mentioned, this recompilation would typically be done by another session.

The assumption that was safe when “Dependant” started to execute, and that was established by
compiling “Dependant”, (that it’s OK to invoke “Parent” statically) is no longer safe[2]. The only safe
action, therefore, is non-negotiably to abandon the current server call.

Here’s simple testcase:

create or replace procedure Parent as
begin
      DBMS_Output.Put_Line('Parent V1');
end;
/

create or replace procedure Dependant authid Current_User is
begin
   execute immediate 'drop procedure Parent';
execute immediate 'create procedure Parent as 
   begin DBMS_Output.Put_Line(''Parent V2''); end;  ';

 Parent();
end Dependant;
/

begin 
 Dependant(); 
end;
/

As presented it causes the ORA-06508 (and knock-on) that you report. But if you invoke “Parent” thus:
execute immediate ‘begin Parent(); end;’;

Then the testcase runs without error and happily says ‘Parent V2’.

[1] Starting with 11.1 or later we have to say this more carefully: when a referenced object is
recompiled (or altered) then each of its dependants for which the change is significant is
invalidated; dependants for which the change is insignificant are not invalidated.

[2] In some circumstances fine-grained dependency checking can establish the safety so that
the need to cause ORA-06508 goes away.

Using Dynamic Sql resolves the error

declare
 v_cnt number;
 v_stat varchar2(20);
 sql_stmt varchar2(200);
 ds_error   varchar2(2000);
begin
 delete from kdummy;
  commit;
 select status into v_stat from user_objects where OBJECT_NAME = 'KTEST';
  insert into kdummy values('1 status of KTEST is ' || v_stat);
SELECT COUNT(*) INTO v_cnt FROM USER_TABLES WHERE TABLE_NAME = 'KDROP';

IF v_cnt > 0 THEN
 sql_stmt:= 'DROP TABLE KDROP';
 EXECUTE IMMEDIATE sql_stmt;
END IF;
select status into v_stat from user_objects where OBJECT_NAME = 'KTEST';
insert into kdummy values('2 status of KTEST after DROP is ' || v_stat);

sql_stmt:= 'create table kdrop (eno number)';
    EXECUTE IMMEDIATE sql_stmt;
select status into v_stat from user_objects where OBJECT_NAME = 'KTEST';
insert into kdummy values('3 status of KTEST after recreate is ' || v_stat);

 
insert into kdummy values('5 Call the procedure ');
commit;

execute immediate 'begin ktest(); end;';

end;
/
Or set the _disable_fast_validate parameter to true, then try again.
alter system set "_disable_fast_validate"=true scope=both;

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 2 views last month,  2 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

IT Tutorial