Rollback + Commit some changes

Question

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 ?

Answer 1

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