Cannot determine which trigger has COMMIT clause

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.

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top