Home » SQL & PL/SQL » SQL & PL/SQL » log ORA-01013: user requested cancel of current operation tips (11g)
log ORA-01013: user requested cancel of current operation tips [message #512797] Wed, 22 June 2011 06:10 Go to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

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 #512809 is a reply to message #512797] Wed, 22 June 2011 06:47 Go to previous messageGo to next message
Littlefoot
Messages: 21564
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you managed to catch it and display a message using DBMS_OUTPUT, how come you can't log it by calling a procedure?

How does that procedure look like? Did you use PRAGMA AUTONOMOUS TRANSACTION in that procedure, which allows you to commit insert into a log table without affecting the "original" transaction?
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

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 #512828 is a reply to message #512826] Wed, 22 June 2011 07:45 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's logging_Package.log?
Re: log ORA-01013: user requested cancel of current operation tips [message #512839 is a reply to message #512828] Wed, 22 June 2011 08:19 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

the log procedure that i call
Re: log ORA-01013: user requested cancel of current operation tips [message #512842 is a reply to message #512839] Wed, 22 June 2011 08:23 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
There seems to be a problem with it:
ORA-06508: PL/SQL: could not find program unit being called: "logging_Package.log"
Re: log ORA-01013: user requested cancel of current operation tips [message #512847 is a reply to message #512842] Wed, 22 June 2011 08:42 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

Yes,
I know.

When i call it directly in a separate block it works.

Re: log ORA-01013: user requested cancel of current operation tips [message #512849 is a reply to message #512847] Wed, 22 June 2011 08:44 Go to previous messageGo to next message
BlackSwan
Messages: 26709
Registered: January 2009
Location: SoCal
Senior Member
>When i call it directly in a separate block it works.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

use COPY & PASTE so we can see what you do & how Oracle responds.
Re: log ORA-01013: user requested cancel of current operation tips [message #512850 is a reply to message #512847] Wed, 22 June 2011 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67163
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But is this accessible to the owner of the calling procedure (or its caller depending how it is defined)?

Regards
Michel

[Updated on: Wed, 22 June 2011 08:45]

Report message to a moderator

Re: log ORA-01013: user requested cancel of current operation tips [message #512862 is a reply to message #512850] Wed, 22 June 2011 10:22 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

both the function and the logging package are under the same schema.

Re: log ORA-01013: user requested cancel of current operation tips [message #512864 is a reply to message #512862] Wed, 22 June 2011 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67163
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the function is defined with "AUTHID DEFINER"?

Regards
Michel
Re: log ORA-01013: user requested cancel of current operation tips [message #512868 is a reply to message #512864] Wed, 22 June 2011 10:41 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

no
Re: log ORA-01013: user requested cancel of current operation tips [message #512870 is a reply to message #512868] Wed, 22 June 2011 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67163
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does this mean it was created with option "AUTHID CURRENT_USER"?

Regards
Michel
Re: log ORA-01013: user requested cancel of current operation tips [message #512872 is a reply to message #512870] Wed, 22 June 2011 11:05 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

i did not used any of the above in the function
Re: log ORA-01013: user requested cancel of current operation tips [message #512873 is a reply to message #512870] Wed, 22 June 2011 11:05 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Thinking about it I'm really confused as to what you are actually doing.
I don't see anyway you can catch an 1013 error inside the DB. If you could you could stop the user from doing it.
So it needs to be caught in the java layer if at all.
That being the case - where does dbms_output come into it?
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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

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.

Smile
Re: log ORA-01013: user requested cancel of current operation tips [message #512982 is a reply to message #512955] Thu, 23 June 2011 03:08 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
What I was getting at is that dbms_output is DB thing and this particular error should really be caught by the client.
The Java passes the input params right?
So it knows what they are. Have it log them when it catches the error - you can still call a DB procedure to log them to a table.
Re: log ORA-01013: user requested cancel of current operation tips [message #513025 is a reply to message #512982] Thu, 23 June 2011 05:53 Go to previous messageGo to next message
vioricamilea
Messages: 23
Registered: March 2009
Location: Bucharest, Romania
Junior Member

yes after all investigation, we will do it through Java.

thanks
Re: log ORA-01013: user requested cancel of current operation tips [message #679631 is a reply to message #512797] Wed, 11 March 2020 08:58 Go to previous message
shthed@gmail.com
Messages: 1
Registered: March 2020
Junior Member
If anyone was interested why the dbms_output didn't happen: EXCEPTION WHEN OTHERS does not catch ORA-01013
see community.oracle.com/thread/3562834
Previous Topic: ORA-02069
Next Topic: Import CSV data from CLOB
Goto Forum:
  


Current Time: Sun Jun 07 05:22:39 CDT 2020