ORA-00604: error occurred at recursive SQL level 1 |How to resolve ORA-00604 error?

ORA-00604: error occurred at recursive SQL level 1 :

In my previous article, I have explained about the most common errors in Oracle. In This article, I will try to explain another most common error, which has been searched approximately 20000 times in a month by DBAs and developers. While working with a database and performing different scenarios of database every developer or dba might have faced error called as ORA-00604: error occurred at recursive SQL level 1. While working with databases I have frequently faced ORA-00604: recursive error and struggled to solve and debug this issue. I would like to share my experience working and debugging this error. This is most common error and very tricky to solve it.

A recursive SQL statement is a statement that is applied to internal dictionary table.

Why ORA-00604 error will come?

There may be multiple reasons for which this error will come. In this section, I will try to explain what will be possible root cause of this error. Because there are many, possible reasons for the error, Oracle simply states that if the situation described in the next error on the stack can be corrected, it should be corrected. Otherwise, the user should contact the Oracle support line.

Reason 1:

Table and view does not Exist

This may be the one possible cause of this error. If due to any reason if one of the table (system table of oracle) is deleted and user tries to insert or update the data in the table this error will occur.

Reason 2:

Trigger Error

This may be another cause of the error. If trigger attempting to insert the records in audit_log table and audit_log table is dropped by cleanup script then this kind of error will come. This kind of error will occur mostly in system triggers.

Reason 3:

User attempts to run newly created table

When user attempts to run the newly created table this error will occure.The package related to the newly created table needs to be compiled to resolve this error.

NO TIME TO READ CLICK HERE TO GET THIS ARTICLE

Resolution of the error:

I have explained that there is no specific reason of this error. There might be the different possible causes of this error, which I have explained above. In this section, I will try to explain the resolutions of this error.

Solution 1:

Check for table availability

Check for whether all tables used in the triggers are available or not in that oracle schema.If the table is not available then user needs to create the table.

Solution 2:

Trigger Issue Resolution

To check whether this issue is because of trigger execution you need to check:

Alter system set “_system_trig_enabled”=FALSE;

View all triggers where trigger_type is before each row and after each row:

SELECT * FROM dba_triggers

WHERE trigger_type not in (‘before each row’,’after each row’);

To find the most relevant triggers, filter the triggering_event column.

Find the trigger that is causing the problem and disable it or drop it to resolve the issue. Usually, this error occurs in the Oracle database by the system level triggers on DDL or SYSTEM events.

Solution 3:

New table creation issue

If this error will occur due to newly created table then user needs to check the related system packages of oracle and compile package specification and body once.

Example:

User needs to recompile DBMS_CDC_PUBLISH package. User needs to compile all invalid packages that are no longer viewed.So this may be the third possible solution to resolve this kind of error.

Hope you like this article.Please don’t forget to share it with everyone.