A table update fails and generates an ORA-00604 error. The error occurred at recursive SQL level 2.
Unfortunately, this error doesn't tell you exactly what the Oracle database was trying to do when the error was raised. When you issue a SQL statement, the Oracle database does a ton of work behind the scenes for you. For instance, consider the following SQL statement:
UPDATE emp SET sal = sal*1.05 WHERE empno=1001;
This SQL statement gives employee number 1001 a 5% pay raise. When you issue this statement, Oracle has to query the data dictionary to determine if this is your table or if you are using a synonym. Once it finds the database object, Oracle queries the data dictionary to determine if you have permissions to access the object. So how does Oracle interact with the data dictionary? It issues a SQL statement of its own. These SQL statements that Oracle issues for you are called "recursive" SQL statements. Your original SQL statement is on level 0. The recursive SQL statement that Oracle issues for you is on level 1. Sometimes, a recursive SQL statement causes a recursive SQL statement of its own, on level 2.
In your case, there is a recursive SQL statement on level 2 being issued that is having a problem. In order to resolve this issue, you'll need to figure out what recursive SQL statement is being executed that is causing this error. To do that, you'll have to start a trace in your session. First, issue the following SQL statement:
ALTER SESSION SET sql_trace=TRUE;
Then, issue your UPDATE statement. You will see the ORA-604 error. Next, issue the following:
ALTER SESSION SET sql_trace=FALSE;
Now go to the directory defined by your database's USER_DUMP_DEST initialization parameter. There should be a file there with a current timestamp. That is the trace file you generated. You can open the file and examine the recursive SQL statements, including the one that caused your error (located near the end of the trace file).
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.