Forum Stats

  • 3,780,542 Users
  • 2,254,408 Discussions
  • 7,879,376 Comments

Discussions

ORA-01013

Manuel Vidigal
Manuel Vidigal Member Posts: 178 Bronze Badge
edited Oct 25, 2021 3:04PM in SQL & PL/SQL

Hi all,

We are upgrading our database from version 12.1.0.2.0 to 19.0.0.0.0 and found a different behaviour in the way ORA-01013 is handled.

We normally catch the ORA-01013 in order to catch timeouts for APIs that are called via TIBCO.

In version 19c, this piece of code doesn't catch the error if execution is broke while in loop:

DECLARE
 e_cancel EXCEPTION;
 PRAGMA EXCEPTION_INIT(e_cancel,
            -01013);
 CURSOR c_objects IS
  SELECT object_name
   FROM dba_objects
  CONNECT BY LEVEL <= 10;
 l_object_name VARCHAR2(100);
BEGIN
 dbms_output.put_line('S
tart of loop');
 FOR x IN c_objects
 LOOP
  l_object_name := x.object_name;
 END LOOP;
 dbms_output.put_line('End of loop');
EXCEPTION
 WHEN e_cancel THEN
  dbms_output.put_line('When e_cancel');
 WHEN OTHERS THEN
  dbms_output.put_line('When others');
END;


But is catched if we break it while executing the sleep API:


DECLARE
 e_cancel EXCEPTION;
 PRAGMA EXCEPTION_INIT(e_cancel,
            -01013);
 CURSOR c_objects IS
  SELECT object_name
   FROM dba_objects
  CONNECT BY LEVEL <= 10;
 l_object_name VARCHAR2(100);
BEGIN
 dbms_output.put_line('Start of loop');
 dbms_session.sleep(60);
 dbms_output.put_line('End of loop');
EXCEPTION
 WHEN e_cancel THEN
  dbms_output.put_line('When e_cancel');
 WHEN OTHERS THEN
  dbms_output.put_line('When others');
END;


Is there any way I can catch the ORA-01013 in order to log the error?

Thanks in advance

Tagged:

Answers