I know 2 “special” exceptions that can’t be processed in exception handler:
- “ORA-01013: user requested cancel of current operation”
- “ORA-03113: end-of-file on communication channel”
- and + “ORA-00028: your session has been killed” from Matthias Rogel
Tanel Poder described the first one (ORA-01013) in details here: https://tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/ where Tanel shows that this error is based on SIGURG signal (kill -URG):
-- 1013 will not be caught:
declare
e exception;
pragma exception_init(e,-1013);
begin
raise e;
exception when others then dbms_output.put_line('caught');
end;
/
declare
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at line 5
ORA-3113 and ORA-28 works in a similar way, but even worse: they also kill a session(process)!
declare
e exception;
pragma exception_init(e,-3113);
begin
raise e;
exception when others then dbms_output.put_line('caught');
end;
/
declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 30787
Session ID: 1 Serial number: 8517
-- 28 will not be caught:
declare
e exception;
pragma exception_init(e,-28);
begin
raise e;
exception when others then dbms_output.put_line('caught');
end;
/
declare
*
ERROR at line 1:
ORA-00028: your session has been killed
ORA-06512: at line 5
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 23223
Session ID: 9 Serial number: 2198
And just for fun: did you know that NO_DATA_FOUND exception has error code +100? (The only error with a positive number 🙂 While it returns “ORA-01403: no data found”.
SQL> select sys.standard.sqlerrm(100) s100,
2 sys.standard.sqlerrm(-1403) s1403
3 from dual;
S100 S1403
---------------------------------------- ----------------------------------------
ORA-01403: no data found ORA-01403: no data found
SQL> exec raise no_data_found;
BEGIN raise no_data_found; END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 1
You can see that both -1403 and +100 return the same ORA-1403.
NO_DATA_FOUND exception is declared in sys.standard and has errorcode = 100 because of ANSI standard:
NO_DATA_FOUND exception;
pragma EXCEPTION_INIT(NO_DATA_FOUND, 100);
And we even can’t define an exception with 1403 🙂
SQL> declare
2 e exception;
3 pragma exception_init(e,100);
4 begin
5 raise e;
6 exception when others then
7 dbms_output.put_line('sqlerrm:'||sqlerrm);
8 dbms_output.put_line('sqlcode:'||sqlcode);
9 end;
10 /
sqlerrm:ORA-01403: no data found
sqlcode:100
PL/SQL procedure successfully completed.
SQL> declare
2 e exception;
3 pragma exception_init(e,-1403);
4 begin
5 raise e;
6 end;
7 /
e exception;
*
ERROR at line 2:
ORA-06550: line 2, column 2:
PLS-00701: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT
PS. Original twitter thread: