My Oracle Support Banner

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 and later
Information in this document applies to any platform.
***Checked for relevance on 06-Jul-2017***


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;
   dbms_output.put_line (' Hi in ktest ');
     select count(*) into vcnt from kdrop;
   dbms_output.put_line (' Count ' || vcnt);

2) Pl/sql block

     v_cnt number;
     v_stat varchar2(20);
     sql_stmt varchar2(200);
     ds_error   varchar2(2000);
     delete from kdummy;
     select status into v_stat from user_objects where OBJECT_NAME = 'KTEST';
      insert into kdummy values('1 status of KTEST is ' || v_stat);
    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);
    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 ');

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


To view full details, 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 a vibrant support community of peers and Oracle experts.