20

I get a weird error while trying to create a trigger in my Oracle 11g database using SQL Developer. Here is what I did:

My table:

CREATE TABLE COUNTRY_CODE(
   ID NUMBER(19,0)      PRIMARY KEY NOT NULL, 
   Code             VARCHAR2(2) NOT NULL,
   Description  VARCHAR2(50),
   created                  TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
   created_by                   VARCHAR2(40) DEFAULT USER, 
   last_updated                 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
   last_updated_by          VARCHAR2(40) DEFAULT USER,
   archived CHAR(1) DEFAULT '0' NOT NULL );

The Sequence:

CREATE SEQUENCE COUNTRY_CODE_ID_SEQ START WITH 1 INCREMENT BY 1;

The trigger:

CREATE OR REPLACE TRIGGER COUNTRY_CODE_TRIGGER
BEFORE INSERT ON COUNTRY_CODE
FOR EACH ROW
DECLARE
    max_id number;
    cur_seq number;
BEGIN
    IF :new.id IS NULL THEN
    SELECT COUNTRY_CODE_ID_SEQ.nextval
    INTO :new.id
    FROM dual;
ELSE
    SELECT GREATEST(NVL(MAX(id),0), :new.id)
    INTO max_id
    FROM COUNTRY_CODE;

    SELECT COUNTRY_CODE_ID_SEQ.nextval
    INTO cur_seq
    FROM dual;

    WHILE cur_seq < max_id
    LOOP
        SELECT COUNTRY_CODE_ID_SEQ.nextval
        INTO cur_seq
        FROM dual;
    END LOOP;
END IF;
END;

Creating the table and the sequence works very well, but when I try to create my trigger, I get this error:

Error report:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
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 created

Does anyone know about this error?

Thanks

1
  • Once you fix the PLScope issue, you're going to get a mutating trigger exception. A row-level trigger on COUNTRY_CODE is not allowed to query the COUNTRY_CODE table. While it is possible to work around this issue with an additional statement-level trigger, it doesn't seem likely that you really need to check every time a row is inserted whether the sequence needs to get reset. It seems much more likely that you'd simply want to set the sequence to a larger value on those rare occasions that something causes to create this error condition. Commented Jun 6, 2013 at 14:05

3 Answers 3

52

I finally found the answer to my problem:

Add this:

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

Or in Oracle SQL Developer:

  1. Go to Tools | Preferences
  2. Select Database | PL/SQL Compiler
  3. Change the PLScope identifiers from All to None
  4. Click on Ok

This fixes the issue...

Sign up to request clarification or add additional context in comments.

1 Comment

That fixed my problem. But why did it occur? What actually was the problem?
3

There may be a solution for this here.

4 Comments

@TonyAndrews Try to avoid answers with only hyperlinks. If the link becomes broken, this answer becomes useless. You may wish to revise your answer to post a summary of what you learned, and then reference the link.
@vapcguy Point taken, however as this answer is 3 years old and not the accepted answer I probably won't!
@TonyAndrews Maybe, if your answer is not link only it would be accepted one as you answered first...
Oh well... This was 5 years ago :-)
3

I have no other solution (and don't have the reputation to merely comment), but here is some information that might help get someone on the right track to solving this problem while still using PL/Scope.

I just had a similar issue, and looking into the PL/Scope feature helped me understand where the problem might come in. For my issue, I tried to create a trigger and the same exact error came up. I changed the body of the trigger to no avail, but changing the name worked fine.

It seems the the PL/Scope was holding onto information about the first instantiated trigger, before dropping it. A query on the triggers revealed my trigger was surely dropped, but a query on the (PL/Scope) identifiers ("all_identifiers") showed it was still there.

Some information on PL/Scope is here: http://www.oracle.com/technetwork/testcontent/o67asktom-101004.html

Chapter 8 here (11g documentation) has more information: http://docs.oracle.com/cd/B28359_01/appdev.111/b28424.pdf

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.