CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
user657592
user657592

Reputation: 1061

ORA-04068: existing state of packages has been discarded

I've wrapper procedure which looks like this:

Procedure A
BEGIN;
    Procedure B
    Procedure C
END;
/

Procedure B drops and re-creates a table used in Procedure C. Due to this, I get the below error:

ERROR at line 1: 
ORA-04068: existing state of packages has been discarded ORA-04065: not executed, altered or dropped stored procedure "SCHEMA.PROCEDURE C" 
ORA-06508: PL/SQL: could not find program unit being called: "SCHEMA.PROCEDURE C" 
ORA-06512: at "SCHEMA.PROCEDURE A", line 4 
ORA-06512: at line 1

How do I go about getting this procedure to work?

Upvotes: 1

Views: 4192

Answers (3)

Jon Heller
Jon Heller

Reputation: 36807

I recommend using dynamic SQL to call the DML in procedure C. By removing the dependency nothing gets invalidated. This would likely be more reliable than dynamically re-compiling code or dynamically running the code and catching all exceptions.

You can see this in the below sample code. Comment out the static SQL call on line 10 in procedure C to replicate the error.

drop table drop_me;
create table drop_me(id number);

create or replace procedure C is
    v_count number;
begin
    --Static SQL would fail with this error:
    --  ORA-04068: existing state of packages has been discarded
    --  ORA-04065: not executed, altered or dropped stored procedure "JHELLER.C"
    --  ORA-06508: PL/SQL: could not find program unit being called: "JHELLER.C"
    --  ORA-06512: at "JHELLER.A", line 4
    --  ORA-06512: at line 2
    --select count(*) into v_count from drop_me;

    --Dynamic SQL runs correctly:
    execute immediate 'select count(*) from drop_me' into v_count;
end;
/
create or replace procedure B is
begin
    execute immediate 'drop table drop_me purge';
    execute immediate 'create table drop_me(id number)';
end;
/
create or replace procedure A is
begin
    B;
    C;
end;
/
begin
    A;
end;
/

But I agree with Alex Poole that there is almost certainly a better way to do this without dropping and re-creating objects. I can think of a few reasons why a DROP and CREATE might run faster than a TRUNCATE and INSERT, but only because of some weird side-effect, like dropping bad table statistics. Investigate the difference more and you might find the real reason.

Upvotes: 2

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

You can do something like it.

 begin 
   procedureB;
   begin 
     execute immediate 'begin procedureC; end;';
   exception when others then 
     execute immediate 'begin procedureC; end;';
   end;   
 end;

Upvotes: 0

Thomas
Thomas

Reputation: 366

So your proc get invalid during the execution, that is why you need recompile Procedure C. To achive this, you can call a dynamic SQL statement:

EXECUTE IMMEDIATE 'ALTER PROCEDURE my_procedure COMPILE';



Procedure_A IS
BEGIN;
    Procedure_B;
    EXECUTE IMMEDIATE 'ALTER PROCEDURE Procedure_C COMPILE';
    Procedure_C;
END;
/

Upvotes: 2

Related Questions