Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle Create trigger statement fails with internal error code ORA-00600

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
like image 240
The Camster Avatar asked Nov 21 '12 20:11

The Camster


1 Answers

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.

like image 111
The Camster Avatar answered Sep 27 '22 18:09

The Camster