IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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 34Note:- Added dbms_session.reset_package; but still the same error
4) Debbugging statements inserted into kdummy table
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 isrecompiled (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;
/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 )
