ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-04092: cannot COMMIT in a trigger ORA-06512: at line 3But 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.
This was first published in June 2006