Home » SQL & PL/SQL » SQL & PL/SQL » How to solved ORA-04068 error (2 threads merged by bb)
How to solved ORA-04068 error (2 threads merged by bb) [message #384016] Sun, 01 February 2009 20:12 Go to next message
paulchen256
Messages: 3
Registered: January 2009
Junior Member
I've some problem like below document. But I don't know what it means. Who can tell me how to do?

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.



[mod-edit: code tags added; next time please add them yourself]

[Updated on: Sun, 01 February 2009 20:58] by Moderator

Report message to a moderator

Re: How to solved ORA-04068 error [message #384018 is a reply to message #384016] Sun, 01 February 2009 20:22 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

>Who can tell me how to do?
Since you neglected to post what actually is causing the error,
I refuse to guess what might be wrong & possible fix.

Are you doing DDL via EXECUTE IMMEDIATE from PL/SQL?
If so, then do not do that.

Re: How to solved ORA-04068 error [message #384019 is a reply to message #384016] Sun, 01 February 2009 20:22 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
do NOT post same problem more than once
Re: How to solved ORA-04068 error [message #384022 is a reply to message #384018] Sun, 01 February 2009 20:58 Go to previous messageGo to next message
paulchen256
Messages: 3
Registered: January 2009
Junior Member
Yes, I do a DDL via EXECUTE IMMEDIATE from PL/SQL.
I need new sequence number when insert into some table as primary key.
My procedure as below.

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"



[mod-edit: code tags added; please start adding them yourself]

[Updated on: Sun, 01 February 2009 20:59] by Moderator

Report message to a moderator

Re: How to solved ORA-04068 error (2 threads merged by bb) [message #384025 is a reply to message #384016] Sun, 01 February 2009 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>FROM NEXT_NUMBER WHERE TABLENAME='SYS' AND FIELDNAME='SYS';
What is this?

post results from: SQL> DESC NEXT_NUMBER

Explain details behind table NEXT_NUMBER; how & why it get populated & accessed.

Is code which you posted being accessed & invoked by USER=SYS?

[Updated on: Sun, 01 February 2009 22:11]

Report message to a moderator

Re: How to solved ORA-04068 error (2 threads merged by bb) [message #384029 is a reply to message #384025] Sun, 01 February 2009 22:34 Go to previous messageGo to next message
paulchen256
Messages: 3
Registered: January 2009
Junior Member
NEXT_NUMBER is a table stored the primary key number
I need the number as ID when insert into some table.
SQL> desc next_number
名稱 空值? 類型
----------------------------------------- -------- ----------------------------
TABLENAME NOT NULL VARCHAR2(5)
FIELDNAME NOT NULL VARCHAR2(20)
VALUE NUMBER(38)

SQL> select * from next_number;

TABLE FIELDNAME VALUE
----- -------------------- ----------
SYS SYS 25713370
Re: How to solved ORA-04068 error (2 threads merged by bb) [message #384034 is a reply to message #384016] Sun, 01 February 2009 22:54 Go to previous message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

Please explain how/why you need both this table & a SEQUENCE?

What updates NEXT_NUMBER; when how & why?
Other than the problematic code which throws errors, how, when, where & why are SELECT statement run against NEXT_NUMBER?

Is code which you posted being accessed & invoked by USER=SYS?

It would be nice you answered questions when 1st ask.
Otherwise folks might cease trying to answer your question(s).

If you do not DROP & CREATE SEQUENCE, then error no longer occur.
Previous Topic: Order By Clause
Next Topic: convert column into rows
Goto Forum:
  


Current Time: Mon Dec 05 06:30:40 CST 2016

Total time taken to generate the page: 0.08675 seconds