Skip to Main Content
  • Questions
  • PL/SQL Procedure - Catching "ORA - 01013 - User Requested Cancel of Current Operation"

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Arnab.

Asked: June 18, 2018 - 1:33 pm UTC

Last updated: June 18, 2018 - 2:50 pm UTC

Version: Oracle Database Version 11.2.0.3.0

Viewed 1000+ times

You Asked

It may be a silly question but I am wondering if there is any way to catch this error "ORA-01013 - User requested cancel of current operation" in a PL/SQL procedure.

The requirement that I have is to update a database record before exiting when the procedure is terminated when

1. User has pressed a control C or some such explicit break commands raising ORA-01013 OR
2. There has been a timeout again resulting in ORA-01013



To explain further on the exact scenario where this can be of help is as below:-

Suppose I am executing a series of DB queries and updating the status to DONE when the query execution is complete. Now when the above error is encountered I would like to update the status of the query to be say "Aborted" before finishing off. The normal PL/SQL exception does not allow me to do this as it appears at the moment.


Apparently this looks impossible but I am looking for any clever way of getting this done if at all possible.


Thanks
Arnab Panja

and Chris said...

No. As MOS note 12838063.8 states, Bug 12838063 fixed the error where you could catch this exception!

PL/SQL exception handlers can catch errors like ORA-3113 or ORA-1013
which should not be trappable by an exception handler. This can lead
to follow on errors, such as ORA-7445 or similar due to incorrect
session state after the error.


So if you want to trap this, you need to do it in the client program.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a lot

Arnab Panja, June 19, 2018 - 4:30 am UTC

Thanks Chris. I was assuming that the client program is the only place possibly where we can trap this error which you have confirmed.

Thanks Again

~Arnab

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library