Search code examples
oracle-databaseplsqltriggersoracle-sqldeveloperfatal-error

I keep receiving Fatal Error/Reset Connection for PL/SQL in Oracle

I am working on a script for a school project, and it requires using a trigger to maintain a history table. I successfully created this trigger earlier, however I had to drop tables and revise a bit. Now that I have finished revising the database, when I try to create the trigger again, I keep receiving the following message.

Error starting at line : 189 in command -
CREATE OR REPLACE TRIGGER WeightChangeTrigger
BEFORE UPDATE OF Current_weight_lbs ON Account
FOR EACH ROW
BEGIN
    INSERT INTO WeightChange (WeightChange_id, Account_id, OldWeight, NewWeight, ChangeDate)
    VALUES (NVL((SELECT MAX(WeightChange_id)+1 FROM WeightChange), 1),
    :NEW.Account_id,
    :OLD.Current_weight_lbs,
    :NEW.Current_weight_lbs,
    TRUNC(SYSDATE));
END;
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 was in an unrecoverable state.
*Action:   Log in to Oracle again so a new server session will be created
           automatically.  Examine the session trace file for more
           information.

My script starts with DROP TABLE commands so that I can alter and re-run the script. The script operates perfectly throughout all of the table and index creation, and then crashes when it encounters the trigger creation. As I mentioned, it was not doing this earlier (the trigger successfully compiled w/o errors), so perhaps I just need to give it some time as it may be a server issue with Oracle?

Additionally, I receive a pop-up window with the following message:

Your database connection has been reset. Any pending transactions or session state has been lost.

I have closed the developer only to reconnect like it suggests, and have the same thing continually happen. Any advice or explanation for why this is happening would be really appreciated. My project is due Thursday morning and so this is giving me much unnecessary anxiety.

Solution

  • ORA-00600 is never good news; it means Oracle bug and there's nothing much you can do about it but search My Oracle Support or raise Service Request.

    Apparently, if you run the following statement, you might get out of the problem:

    ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

    Additionally, this might help:

    ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated

    Try to simply rename the trigger and run the script again.