Q
Problem solve Get help with specific problems with your technologies, process and projects.

Cannot determine which trigger has COMMIT clause

I am getting these errors: "ORA-00604: error occurred at recursive SQL level 1," "ORA-04092: cannot COMMIT in a trigger," "ORA-06512: at line 3." But when I try to determine which trigger has the COMMIT clause in it with the query, I have no row returned.

I am getting these two errors:
ERROR: ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at line 3
But when I try to determine which trigger has the COMMIT clause in it with the query, I have no row returned. How can I solve the problem? Thanks in advance for your assistance.

If you query DBA_SOURCE and do not find any trigger that does not have a COMMIT statement in it, then it is most likely that the trigger is calling a subprogram (i.e., another procedure) which does have a COMMIT it in. At this point, I would suggest starting a SQL trace in your session, run the code which produces the error, and then look at the resulting trace file. To start a trace in your session, use the following command:

ALTER SESSION SET SQL_TRACE=TRUE;

Exercise your code which results in the ORA-4092 error. Once the error has been reached, exit your session. On the database server in the USER_DUMP_DEST directory, you will find a trace file for your session. You'll have to read through the contents of the trace file to see which trigger is causing you grief. For information on how to read the raw trace file, look on my Web site for a white paper which describes this process.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close