CODEWITHSUNDEEP
sqloracle-databaseplsql
Narasimha Maiya
Narasimha Maiya

Reputation: 1029

Rollback for error log doesn't work why?

I know that rollback should not work on error logs, but in a logical way it should. Let me consider the following example.

CREATE OR REPLACE procedure error_ins
AS
BEGIN
   INSERT INTO dummy VALUES(4) LOG ERRORS INTO ERROR_LOG_DMLEL;
   INSERT INTO dummy VALUES(2) LOG ERRORS INTO ERROR_LOG_DMLEL;
   INSERT INTO dummy VALUES(7) LOG ERRORS INTO ERROR_LOG_DMLEL;
   COMMIT;
   EXCEPTION
     WHEN OTHERS THEN
         ROLLBACK;
         dbms_output.put_line('error ocured');
END;

Here the values of dummy table will be rollback to previous state when exception occurs but error recorded by the ERROR_LOG_DMLEL table will not.

It's good that rollback dose't work for error log, but why it's not working?

Or is their something else that i need to consider?

I gone through several documentation I could't find any reference on this.

If anyone know the reason please write back.

Thank you.

Upvotes: 1

Views: 454

Answers (1)

Hellmar Becker
Hellmar Becker

Reputation: 2972

The logging is not part of the main transaction. See here for a detailed explanation: http://www.oracle-developer.net/display.php?id=329. If error logging happened inside the main transaction, and the transaction failed, you would lose the error log with it, which is not what you usually want.

On a related note, this is why custom logging routines use PRAGMA AUTONOMOUS TRANSACTION.

Upvotes: 6

Related Questions