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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With