• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9100
  • Last Modified:

ORA-01013: user requested cancel of current operation

A java application is failing due to Oracle Error: ORA-01013: user requested cancel of current operation.

Extract from Log is as below

SQLException for SQL [{call mySCHEMA.myPKG.myPROC(?, ?, ?, ?, ?)}]; SQL state [72000]; error code [1013]; ORA-01013: user requested cancel of current operation
ORA-06512: at "mySCHEMA.myPKG.myPROC", line 765
ORA-06512: at line 1
; nested exception is java.sql.SQLException: ORA-01013: user requested cancel of current operation
ORA-06512: at "mySCHEMA.myPKG.myPROC", line 765
ORA-06512: at line 1


0
gram77
Asked:
gram77
  • 7
  • 5
2 Solutions
 
JacobfwCommented:
There are several reasons that this error may be occuring such as a timeout or too many records returned or others.  

Can you provide a snipet of the SQL code?
Are there any trace files happening on the Oracle side?
0
 
gram77Author Commented:
        1 package body myPKG
            ..
       703    procedure myPROC (
       704                                   p_mode            in     number,
                                    ..
       710                                  )
       711    is
            ..
       714    begin
            ..
       730       case p_mode
       731          when 0 then
                  ..
       733             load_abc (p_package_nme     => p_package_nme,
       734                             p_region_id       => p_region_id,
       735                             p_as_of_dt        => p_as_of_dt);
       736          else
       737             --Populate global interest list
       738             myPROC (p_as_of_dt => p_as_of_dt, p_mode => p_mode);
       739
       740             if p_mode = 2 then
       741                report_unresolved ();
       742             end if;
       743       end case;
       744
            ..
       750       commit;
       751    exception
       752       when others then
       753          rollback;
       754          v_err_cd       := 1;
                  ..
       765          raise;
       766    end myPROC;
       841 end myPKG;
0
 
JacobfwCommented:
Is Line 738 calling myPROC the same procedure that is currently running?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
JacobfwCommented:
Looks like you are raising an exception withing an exception handling.
Do you want to remove the line 765 Raise and determine what exception is happening to trigger the "When Others"?
Did you mean to reraise the exception?
    http://download.oracle.com/docs/cd/B13789_01/appdev.101/b10807/07_errs.htm#i3359
0
 
gram77Author Commented:
      1 package body myPKG
            ..
       703    procedure myPROC (
       704                                   p_mode            in     number,
                                    ..
       710                                  )
       711    is
            ..
       714    begin
            ..
       730       case p_mode
       731          when 0 then
                  ..
       733             load_abc (p_package_nme     => p_package_nme,
       734                             p_region_id       => p_region_id,
       735                             p_as_of_dt        => p_as_of_dt);
       736          else
       737             --Populate global interest list
       738             myPROC1 (p_as_of_dt => p_as_of_dt, p_mode => p_mode);
       739
       740             if p_mode = 2 then
       741                report_unresolved ();
       742             end if;
       743       end case;
       744
            ..
       750       commit;
       751    exception
       752       when others then
       753          rollback;
       754          v_err_cd       := 1;
                  ..
       765          raise;
       766    end myPROC;
       841 end myPKG;
0
 
devalkpCommented:
Hi,

Try one of the following
- If this error is generated in response to the user cancelling their current operation then the message is correct and no remedial action is required. (Although it may be pertinent to understand why the user cancelled the operation.)
- If the error is preceded by any other error then it is likely that it is the preceding error which needs to be investigated.
- Timeouts (whether explicitly or implicitly specified or reported) are a frequent cause of ORA-01013. This can be difficult to identify because a timeout may not be explicitly reported as part of the error.
     - The Oracle ODBC driver implements a timeout
     - SQL*Net also implements a timeout. This is specified via the 'sqlnet.expire_time' parameter in the 'sqlnet.ora' file.
0
 
gram77Author Commented:
line# 765, is exception section stement, does it mean this exception is causing this exception
0
 
JacobfwCommented:
Line 765 is reraising the exception that caused the original code to flow to the "when others" exception.

This probably just accounts for the exception showing twice in your log.

Reraising may be valid if you are then handleing this excepton from a calling routine or procedure.

More than likely you need to identify the statement line in the above code that is causing the 'When others' exception to be called.  Does this appear to be a timeout issue (ie taking about 30 seconds to fail)?
0
 
gram77Author Commented:
error is happening on a merge statement, is a timeout happening due to a large merge?
            merge into myTABLE1
            using      (                                                
                        select  mycol1,
                                mycol1
                        from       myTABLE2
                                inner join
                                   myTABLE3
                                on (
                        where   ia.inst_vndr_src_id = 'ADP'
                        union
                  ..
                        union
                  ..
            when matched then
            ..
            when not matched then
            ..
0
 
gram77Author Commented:
Seems to be a timeout issue, see the log below:

10-OCT-11 11.37.34.202257000 PM           "[myPKG][1081-1][myPROC]{}
10-OCT-11 11.07.31.931592000 PM           [myPKG][1081-1][myPROC3]


details:
10-OCT-11 11.37.34.202257000 PM           "[myPKG][1081-1][myPROC]{}
Stack:
ORA-01013: user requested cancel of current operation
Backtrace:
ORA-06512: at "mySCHEMA.myPKG", line 455
ORA-06512: at "mySCHEMA.myPKG", line 738
"              12420345             10
10-OCT-11 11.07.31.931592000 PM           [myPKG][1081-1][myPROC3]
10-OCT-11 11.07.31.868332000 PM           [myPKG][1081-1][myPROC3]
10-OCT-11 11.07.31.867889000 PM           [myPKG][1081-1][myPROC2]
10-OCT-11 11.07.31.866831000 PM           [myPKG][1081-1][myPROC1]
10-OCT-11 11.07.31.865893000 PM           [myPKG][1081-1][myPROC1]
0
 
JacobfwCommented:
Are you using an ODBC connector?
Can you uncheck 'Enable Query Timeout' in the DSN settings?
0
 
gram77Author Commented:
got answer
0
 
gram77Author Commented:
please close this question
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now