How to solved ORA-04068 error (2 threads merged by bb) [message #384016] |
Sun, 01 February 2009 20:12  |
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 #384022 is a reply to message #384018] |
Sun, 01 February 2009 20:58   |
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 #384029 is a reply to message #384025] |
Sun, 01 February 2009 22:34   |
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  |
 |
BlackSwan
Messages: 26766 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.
|
|
|