Symptoms

This note focuses on one cause of ORA-04068 and provides a reproducible testcase. Executing the procedure "calling_ins_test" from code below that raises 

    ORA-04068: existing state of packages has been discarded

and invalidates another procedure "insert_test1" when the later is called.


Cause
Procedure "calling_ins_test" was also issuing some DDL to drop and re-recreate a SEQUENCE to reset the sequence value.  This sequence was in turn used by "insert_test1", which then caused a dependency.

connect system/manager
grant create sequence to scott;
connect scott/tiger
set serveroutput on

create or replace procedure man_seq is
begin
  begin
  execute immediate 'drop sequence t_seq';
  exception
    when others then
    dbms_output.put_line('sequence does not exist');
  end;
  execute immediate 'create sequence t_seq';
end;
/

drop table test1;

create table test1 ( id number(2));

begin
  man_seq;
end;
/

create or replace procedure insert_test1 is
begin
  insert into test1 values (t_seq.nextval);
end;
/

create or replace procedure calling_ins_test is
begin
  man_seq; -- re-recreate sequence ( insert_test1 now invalid)
  insert_test1;
end;
/
 
begin
  calling_ins_test;
end;
/
 
    

Output:

Connected.
Grant succeeded.
Connected.
Procedure created.
Table dropped.
Table created.
PL/SQL procedure successfully completed.
Procedure created.
Procedure created.
begin
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure
"SCOTT.INSERT_TEST1"
ORA-06508: PL/SQL: could not find program unit being called:
"SCOTT.INSERT_TEST1"
ORA-06512: at "SCOTT.CALLING_INS_TEST", line 4
ORA-06512: at line 2

Solution

    * Replace the sequence by a local variable that is passed into the calling procedure and manually    incremented for inserts.

      or

    * Replace the sequence by a public package variable and manually increment it for inserts.

CREATE OR REPLACE PROCEDURE CAT_IMP_AMCNRESREQ_OPEN
AS
BEGIN

       CAP_SEQ_TEST;
      
        INSERT INTO RESOURCES_REQUIRED (RESREQ_ID, REQ_TYPE_CODE, RECORD_KEY, RESOURCE_ID, USAGE_QTY, CONSUMPTION_CODE, CONSUMPTION_RATE)
        SELECT  cas_test.nextval,'A',A1.AVLMCN_ID,A1.RESOURCE_ID,1,'AS',1
        FROM (SELECT DISTINCT AVLMCN_ID,RESOURCE_ID FROM  CAT_AMCNRESREQ_TEMP) A1;
                                
        COMMIT;   
         
END;

CREATE OR REPLACE PROCEDURE CAP_SEQ_TEST
AS
    V_START     VARCHAR(200);
    V_C0        INTEGER;
BEGIN

   SELECT COUNT(*) INTO V_C0 FROM USER_OBJECTS WHERE OBJECT_TYPE='SEQUENCE' AND OBJECT_NAME='CAS_TEST';
   IF  V_C0 = 1 THEN 
       EXECUTE IMMEDIATE 'drop sequence cas_test';
   END IF;
   SELECT 'CREATE SEQUENCE CAS_TEST START WITH ' || TO_CHAR("VALUE") ||  ' INCREMENT BY 1' INTO V_START 
        FROM NEXT_NUMBER WHERE TABLENAME='SYS' AND FIELDNAME='SYS';
   EXECUTE IMMEDIATE V_START;    

END;

Error as
ORA-04068: 套裝程式  的現行狀態已被捨棄
ORA-04065: 未執行, 更改或刪除 stored procedure "CHIPMOS.CAT_IMP_AVAILMCN_OPEN"
ORA-06508: PL/SQL: 找不到正在呼叫的程式單元: "CHIPMOS.CAT_IMP_AVAILMCN_OPEN"