6

Trying to execute a dumpfile created from Oracle. Everything gets created and altered fine until this block:

CREATE OR REPLACE TRIGGER "LABS"."CHANNEL_CHANNEL_ID_TRG" BEFORE INSERT ON channel
FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.CHANNEL_ID IS NULL THEN
SELECT  channel_CHANNEL_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
-- If this is the first time this table have been inserted into (sequence == 1)
IF v_newVal = 1 THEN 
  --get the max indentity value from the table
  SELECT NVL(max(CHANNEL_ID),0) INTO v_newVal FROM channel;
  v_newVal := v_newVal + 1;
  --set the sequence to that value
  LOOP
       EXIT WHEN v_incval>=v_newVal;
       SELECT channel_CHANNEL_ID_SEQ.nextval INTO v_incval FROM dual;
  END LOOP;
END IF;
--used to emulate LAST_INSERT_ID()
--mysql_utilities.identity := v_newVal; 
-- assign the value from the sequence to emulate the identity column
:new.CHANNEL_ID := v_newVal;
END IF;
END;

This fails and severs the connection, giving the following rather cryptic error:

ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg1], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
00603. 00000 -  "ORACLE server session terminated by fatal error"
*Cause:    An ORACLE server session is in an unrecoverable state.
*Action:   Login to ORACLE again so a new server session will be create
2
  • ORA-00600 indicates an oracle bug, in this case seemingly an interaction between import and PL/Scope. In this case your workaround (turn off PL/Scope) is fine. If you need to use PL/Scope, you can re-enable it and recompile the trigger. Commented Nov 22, 2012 at 4:59

1 Answer 1

9

Found a solution that worked. Surround the block of code with the following ALTER statements:

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

... sql statements here ...

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL'; 

For some reason, the Oracle database dump does not include these commands, but this fixed the problem.

Not the answer you're looking for? Browse other questions tagged or ask your own question.