HAndling Exception when user cancels procedure ORA-01013

Question

We have a procedure that processes a lot of records in the database. Now sometimes if the procedure is taking too long the user cancels the procedure manually thus throwing a ORA-01013 EXCEPTION. However we would still want to know how many records were processed before the procedure was cancelled. We tried to generate a log that was called in the EXCEPTION WHEN OTHERS block but any code in that block does not seem to work. We even tried raising an EXCEPTION by the PRAGMA INIT EXCEPTION for the ORA-01013 with no avail. The code seems to reach the exception but does not execute any code in it. I am guessing that since the procedure is cancelled the code in the EXCEPTION block does not have the time to do anything and is just shut down.

Any idea how to get a count of the records processed before the procedure was cancelled? I could try to increase the record everytime a commit happens but was wondering if there was a better way to achieve this

Answer 1

You could log the progress in an autonomous transaction.

i.e. log the rows processed into a log table in a seperate transaction (with its own commits) via the 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 cancelled then you can query the data logged by the AT and find out how many rows had been processed as the record inserts in the log table will not be rolled back by your "main" transaction.

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

BTW, you can put (pretty much) whatever code you want into an exception section and it will be executed if that exception is raised. There must be another reason why your code is either not being run or is being rolled back by the transaction that has caused the exception to be raised.

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

Hope it helps...

Answer 2

I just experienced the same thing - this seems to be an undocumented bug:

ORA-01013 is simply not catched by WHEN OTHERS... I have to add an explicit WHEN ORA-01013 Block to catch and handle the exception... Allthough the documentation explicitly states that WHEN OTHERS will catch ALL RUNTIME EXCEPTIONS -.-

Code to test it:

This code should always print 'EXCEPTION OTHERS' but will print 'EXCEPTION CANCELLED' on 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;
/