HAndling Exception when a user cancels an ORA-01013 procedure

Question

We have a procedure that processes a lot of records in the database. Now sometimes, if the procedure takes too much time, the user manually cancels the procedure, thereby throwing ORA-01013 EXCEPTION. However, we still want to know how many records were processed before the procedure was canceled. We tried to create a log that was called in the EXCEPTION WHEN OTHERS block, but any code in this block does not work. We even tried to raise PRAGMA INIT EXCEPTION EXCLUSION for ORA-01013 to no avail. It seems that the code reaches the exception, but does not execute any code in it. I assume that since the procedure is canceled, the code in the EXCEPTION block does not have time to do anything and just shuts down.

Any idea how to get the number of processed records before the procedure has been canceled? I could try to increment the record every time a commit occurs, but I was wondering if there is a better way to achieve this.

Answer 1

You can register progress in an offline transaction .

i.e. write the rows processed into the log table in a separate transaction (with its own commits) via AT, something like:

 CREATE OR REPLACE PROCEDURE log_progress ( p_id IN NUMBER, p_data IN VARCHAR2 ) AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO log_table ( id, logging_data ) VALUES ( p_id, p_data ); -- COMMIT; EXCEPTION WHEN others THEN -- Log the error here ... -- Re-raise if needed RAISE; END; 

If the process is canceled, you can request the data registered in AT and find out how many rows were processed, since the insertion entries in the log table will not be canceled by your "main" transaction.

Another method would be to write to the log file using the UTL_FILE package, and then read the contents of the file if the transaction is canceled.

By the way, you can put (pretty much) any code you want into the exception section, and it will be executed if that exception is raised. There must be another reason why your code either fails or is rolled back by the transaction that raised the exception.

http://www.exforsys.com/tutorials/oracle-11g/oracle-11g-exception-handling.html

Hope this helps ...

Answer 2

I just experienced the same thing - this seems like an undocumented error:

ORA-01013 just doesn’t get caught WHEN OTHER ... I have to add an explicit WHEN block ORA-01013 to catch and handle the exception ... Although the documentation explicitly states that WHEN OTHER will catch ALL EXCEPTIONS RUNTIME -.-

Verification Code:

This code should always print EXCLUSIVE OTHERS, but print EXCEPTION CANCELED at 11g - can anyone confirm this behavior?

 DECLARE e_cancelled EXCEPTION; PRAGMA EXCEPTION_INIT(e_cancelled, -1013); BEGIN BEGIN RAISE e_cancelled; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS'); END; EXCEPTION WHEN e_cancelled THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION CANCELLED'); END; /