Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Error ORA-00604 (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production)
Trigger Error ORA-00604 [message #673860] Thu, 13 December 2018 07:10 Go to next message
wtolentino
Messages: 301
Registered: March 2005
Senior Member
i compiled a trigger and it returns an error message "ORA-00604: error occurred at recursive SQL level 1".

SQL> CREATE OR REPLACE TRIGGER FTHADBA.FTHA_VIOLATION_TRG
  2  BEFORE INSERT
  3  ON FTHADBA.FTHA_VIOLATION
  4  REFERENCING OLD AS OLD NEW AS NEW
  5  FOR EACH ROW
  6  BEGIN
  7    <<COLUMN_SEQUENCES>>
  8    BEGIN
  9      IF INSERTING AND :NEW.ID_VIOLATION IS NULL THEN
 10        SELECT FTHA_VIOLATION_SEQ.NEXTVAL INTO :NEW.ID_VIOLATION FROM SYS.DUAL;
 11      END IF;
 12    END COLUMN_SEQUENCES;
 13  END;
 14  /
CREATE OR REPLACE TRIGGER FTHADBA.FTHA_VIOLATION_TRG
                                  *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01405: fetched column value is NULL

please help. thank you.

note: i had googled about the error and yes there is but not exactly the same as to this trigger problem.
Re: Trigger Error ORA-00604 [message #673861 is a reply to message #673860] Thu, 13 December 2018 07:18 Go to previous messageGo to next message
John Watson
Messages: 8268
Registered: January 2010
Location: Global Village
Senior Member
Can you work out which structure is hitting the problem? For example, does this compile:
CREATE OR REPLACE TRIGGER FTHADBA.FTHA_VIOLATION_TRG
    BEFORE INSERT
    ON FTHADBA.FTHA_VIOLATION
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    BEGIN
      <<COLUMN_SEQUENCES>>
      BEGIN
      NULL;
     END COLUMN_SEQUENCES;
   END;
   /
Re: Trigger Error ORA-00604 [message #673862 is a reply to message #673861] Thu, 13 December 2018 07:29 Go to previous messageGo to next message
wtolentino
Messages: 301
Registered: March 2005
Senior Member
SQL> CREATE OR REPLACE TRIGGER FTHADBA.FTHA_VIOLATION_TRG
  2  BEFORE INSERT
  3  ON FTHADBA.FTHA_VIOLATION
  4  REFERENCING OLD AS OLD NEW AS NEW
  5  FOR EACH ROW
  6  BEGIN
  7    <<COLUMN_SEQUENCES>>
  8    BEGIN
  9      NULL;
 10    END COLUMN_SEQUENCES;
 11  END;
 12  /
CREATE OR REPLACE TRIGGER FTHADBA.FTHA_VIOLATION_TRG
                                  *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01405: fetched column value is NULL

same error.
Re: Trigger Error ORA-00604 [message #673863 is a reply to message #673862] Thu, 13 December 2018 07:32 Go to previous messageGo to next message
John Watson
Messages: 8268
Registered: January 2010
Location: Global Village
Senior Member
Well, go on: try a few other variations. I'm not going to write fifty triggers for you. Just start with the simplest possible code, and take it from there:
orclx>
orclx> create trigger tr1 before insert on emp begin
  2  null;
  3  end;
  4  /

Trigger created.

orclx>
Re: Trigger Error ORA-00604 [message #673864 is a reply to message #673860] Thu, 13 December 2018 07:34 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Look for a DDL trigger on the DB.
That'll be the culprit (or you've got an oracle bug).
Re: Trigger Error ORA-00604 [message #673866 is a reply to message #673860] Thu, 13 December 2018 08:29 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
wtolentino wrote on Thu, 13 December 2018 08:10
i compiled a trigger and it returns an error message "ORA-00604: error occurred at recursive SQL level 1".

[code]
...
4 REFERENCING OLD AS OLD NEW AS NEW
...
10 SELECT FTHA_VIOLATION_SEQ.NEXTVAL INTO :NEW.ID_VIOLATION FROM SYS.DUAL;
...
This is not your error, but I never understood why anyone uses "REFERENCING OLD AS OLD NEW AS NEW." This is equivalent to saying "when I say old, I mean old and when I say new, I mean new." What's the point?

And don't reference DUAL and SYS.DUAL.

Re: Trigger Error ORA-00604 [message #673867 is a reply to message #673866] Thu, 13 December 2018 08:39 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
joy_division wrote on Thu, 13 December 2018 14:29

This is not your error, but I never understood why anyone uses "REFERENCING OLD AS OLD NEW AS NEW." This is equivalent to saying "when I say old, I mean old and when I say new, I mean new." What's the point?
While I agree we can blame the examples in the oracle docs for that.
joy_division wrote on Thu, 13 December 2018 14:29

And don't reference DUAL and SYS.DUAL.

That second and should as, yes?
Re: Trigger Error ORA-00604 [message #673868 is a reply to message #673867] Thu, 13 December 2018 08:53 Go to previous messageGo to next message
wtolentino
Messages: 301
Registered: March 2005
Senior Member
actually the code is written by our application developers. i am a DBA and we promoting the code to another database. in other database it works fine but not on the database that I am trying to compile it.

so I tried some work around like using a different trigger name but same code and it works.

SQL> CREATE OR REPLACE TRIGGER FTHADBA.FTHA_VIOLATION_TRG_1
  2  BEFORE INSERT
  3  ON FTHADBA.FTHA_VIOLATION
  4  REFERENCING OLD AS OLD NEW AS NEW
  5  FOR EACH ROW
  6  BEGIN
  7    <<COLUMN_SEQUENCES>>
  8    BEGIN
  9      IF INSERTING AND :NEW.ID_VIOLATION IS NULL THEN
 10        SELECT FTHADBA.FTHA_VIOLATION_SEQ.NEXTVAL INTO :NEW.ID_VIOLATION FROM SYS.DUAL;
 11      END IF;
 12    END COLUMN_SEQUENCES;
 13  END;
 14  /

Trigger created.

so this gives me an idea to drop and create the same trigger and that works.
SQL> CREATE OR REPLACE TRIGGER FTHADBA.FTHA_VIOLATION_TRG
  2  BEFORE INSERT
  3  ON FTHADBA.FTHA_VIOLATION
  4  REFERENCING OLD AS OLD NEW AS NEW
  5  FOR EACH ROW
  6  BEGIN
  7    <<COLUMN_SEQUENCES>>
  8    BEGIN
  9      IF INSERTING AND :NEW.ID_VIOLATION IS NULL THEN
 10        SELECT FTHADBA.FTHA_VIOLATION_SEQ.NEXTVAL INTO :NEW.ID_VIOLATION FROM SYS.DUAL;
 11      END IF;
 12    END COLUMN_SEQUENCES;
 13  END;
 14  /

Trigger created.

and when tried to recreate the same trigger without dropping it works this time.
Re: Trigger Error ORA-00604 [message #673873 is a reply to message #673867] Thu, 13 December 2018 12:24 Go to previous message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Thu, 13 December 2018 09:39
joy_division wrote on Thu, 13 December 2018 14:29

This is not your error, but I never understood why anyone uses "REFERENCING OLD AS OLD NEW AS NEW." This is equivalent to saying "when I say old, I mean old and when I say new, I mean new." What's the point?
While I agree we can blame the examples in the oracle docs for that.
joy_division wrote on Thu, 13 December 2018 14:29

And don't reference DUAL and SYS.DUAL.

That second and should as, yes?
Yes cookiemonster, I have mentioned in the past that I am an expert typo-ist. I have to do a a better job of proofreading.
Previous Topic: Question on rank over partition
Next Topic: Cannot insert specific char
Goto Forum:
  


Current Time: Wed May 27 16:32:16 CDT 2020