Tips to Resolve ORA-4068 When an Object is Explicity Altered (Doc ID 577586.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 9.2.0.8 and later
Information in this document applies to any platform.
***Checked for relevance on 06-Jul-2017***

Symptoms

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 "SCOTT.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

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms