|
|
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 #512875 is a reply to message #512873] |
Wed, 22 June 2011 11:27 |
cookiemonster
Messages: 13925 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.
|
|
|
|
|
|
|