Rollback + Commit some changes

oracle, plsql

Solution

There is, I would normally expect a centralized logging routine and in that logging package, in the function you use to write the log, insert this line after the procedure header declaration

  PRAGMA AUTONOMOUS_TRANSACTION;

That will place the log insertions on a different transaction to the calling routine, allowing you to commit the log regardless of what happens in the parent routine.

Docs Details : http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/autotransaction_pragma.htm

Problem

I have a case where I need to Rollback changes on the Database, but also, I want to keep the inserted Logs. Illustration: ``` Add processing log Insert some data Insert some data Insert some data Add processing log Do some checking... Checking Failed Add processing log Rollback; ``` - Log table on the same database of the other inserted data Is there any way to achieve this ?

Original source