Skip to Main Content
  • Questions
  • Recompilation of Trigger Fails with "ORA-01405: fetched column value is NULL"

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Cherry.

Asked: March 31, 2021 - 8:38 am UTC

Last updated: April 07, 2021 - 3:35 am UTC

Version: 12.2.0.1.0

Viewed 1000+ times

You Asked

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?

and Connor said...

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;


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library