| Trigger Error ORA-00604 [message #673860] |
Thu, 13 December 2018 07:10  |
wtolentino
Messages: 398
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  |
John Watson
Messages: 8929
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 |
wtolentino
Messages: 398
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 |
John Watson
Messages: 8929
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 #673866 is a reply to message #673860] |
Thu, 13 December 2018 08:29 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
wtolentino wrote on Thu, 13 December 2018 08:10i 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 |
cookiemonster
Messages: 13920
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 |
wtolentino
Messages: 398
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 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
cookiemonster wrote on Thu, 13 December 2018 09:39joy_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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
