| log ORA-01013: user requested cancel of current operation tips [message #512797] |
Wed, 22 June 2011 06:10  |
|
|
Hi,
I am having a pipeline function that is called from Java interface.
The function is suppose to return a list of events.
If it takes too long the customer might cancel it.
I would like to catch in a log table all the information of the input param when the request is canceled.
I tried to log every time the sqlcode is -1013, if I dbms_output something then it works, but if i try to call a procedure to do logging, it does not work, or if i try to insert directly in a log table also is not working.
Do you have any idea if is possible to catch(log) this kind of exception.
Thank you.
|
|
|
|
|
|
| Re: log ORA-01013: user requested cancel of current operation tips [message #512811 is a reply to message #512809] |
Wed, 22 June 2011 06:51  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
This may be nothing, but I've experienced something like what the OP describes, but mines is (definitely) a GUI thing:
Basically if one cancels a long running operation in SQL Developer, then you do something else, the next action will sometimes fail with "user requested cancel...etc", any subsequent operations are fine.
Possibly related, possibly nothing.
|
|
|
|
| Re: log ORA-01013: user requested cancel of current operation tips [message #512826 is a reply to message #512811] |
Wed, 22 June 2011 07:41 |
|
|
Yes i have PRAGMA AUTONOMUS TRANSACTION in the log procedure that i call.
This is the error that PL/SQL developer is showing after i cancel the function call.
ORA-01013: user requested cancel of current operation
ORA-06508: PL/SQL: could not find program unit being called: "logging_Package.log"
ORA-06512: at "pipeline_function", line 99
ORA-01013: user requested cancel of current operation
ORA-06512: at line 1
I have called the procedure from the logging package in a anonymous block and worked just perfect.
Any idea?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: log ORA-01013: user requested cancel of current operation tips [message #512875 is a reply to message #512873] |
Wed, 22 June 2011 11:27 |
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Apparently you can catch 1013 errors inside the DB.
However it does appear to behave differently to other errors in subtle ways:
First a simple procedure that'll take ages to complete:
SQL> CREATE OR REPLACE PROCEDURE canc_test AS
2 BEGIN
3 for rec in (select a.* from all_objects a, all_objects b) loop
4 null;
5 end loop;
6 end;
7 /
Procedure created.
SQL> exec canc_test;
^C^C^CBEGIN canc_test; END;
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "LIVE.CANC_TEST", line 3
ORA-06512: at line 1
Errored out with a line number
Now lets add an exception handler
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE canc_test AS
2 BEGIN
3 for rec in (select a.* from all_objects a, all_objects b) loop
4 null;
5 end loop;
6 EXCEPTION WHEN OTHERS THEN
7 dbms_output.put_line('caught!!!!!');
8 end;
9 /
Procedure created.
SQL> set serveroutput on
SQL> exec canc_test;
^CBEGIN canc_test; END;
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
You'll notice that we didn't get the dbms_output but neither did we get the full error stack as above.
I decided to double check serveroutput:
SQL> begin
2 dbms_output.put_line('caught!!!!!');
3 end;
4 /
caught!!!!!
caught!!!!!
PL/SQL procedure successfully completed.
Hmmmm - So presumably the original dbms_output got stored but the error stopped sqlplus from retrieving it so it sat in the buffer.
So let's move the error handler:
SQL> CREATE OR REPLACE PROCEDURE canc_test AS
2 BEGIN
3 for n in 1..100 loop
4 begin
5 for rec in (select a.* from all_objects a, all_objects b) loop
6 null;
7 end loop;
8 EXCEPTION WHEN OTHERS THEN
9 dbms_output.put_line('caught!!!!!');
10 end;
11 end loop;
12 end;
13 /
Procedure created.
SQL> exec canc_test;
^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C^C
That's still running. Ooooops! Just had to go in as a DBA user and kill the session.
Just as well it's a dev server.
I'll that to my list of reasons not to use exception when others.
@vioricamilea - I really strongly recommend catching this error in the java layer.
|
|
|
|
| Re: log ORA-01013: user requested cancel of current operation tips [message #512955 is a reply to message #512875] |
Thu, 23 June 2011 01:43 |
|
|
Thanks, for all your work.
There was a question above why i used dbms_output?
I used it to test it, because it was not logging anything when i was cancel the execution, so i used dbms_output to see where it stops.
My colleague found a different behavior in 10g and 11g regarding this.
I will post the solution when we will mange to make it work.
|
|
|
|
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
