The error below shown when I recompile a trigger with a new version in a productive database. However, I can recompile the same trigger successfully in a test database (Both the productive and test database are in version 12.2.0.1.0).
Error report -
ORA-00604: error occurred at recursive SQL level 1
ORA-01405: fetched column value is NULL
00604. 00000 - "error occurred at recursive SQL level %s"
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.
I saw a statement on the Oracle support page but I couldn't verify if drop and compile again the trigger works as I couldn't replicate this error in the test database (I have a concern about dropping the existing trigger and still not able to compile the new trigger in the productive database):
The following "CREATE OR REPLACE TRIGGER" statement on an existing trigger fails with "ORA-01405" after application of Database Oct 2018 Release Update 12.2.0.1.181016(<patch 28662603>).
The trigger compilation goes successful if the trigger is dropped first and then compiled again with the same "CREATE OR REPLACE TRIGGER" statement.The starting of my trigger code is as below:
CREATE OR REPLACE TRIGGER INSERT_TABLE_TR
BEFORE INSERT ON table1
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
...
Why is the trigger recompilation in the productive database raise this error but success in the test database?
How can I replicate this error in the test database so that I could test out the suggestion?
Given that this is a bug, its unlikely you can control when it happens, but perhaps try this method to control things in Production:
CREATE OR REPLACE TRIGGER INSERT_TABLE_TR_NEW_NAME
...
...
DISABLE;
which creates a new version of the trigger (ie, new name) in Production but it is disabled - this will check that compilation works without problems.
Then you could do
drop trigger INSERT_TABLE_TR
alter trigger INSERT_TABLE_TR_NEW_NAME enable;