HAndling Exception when user cancels procedure ORA-01013

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...

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;/