Inskipp the uncatchable exception

The world is full of exceptions. And exception handlers.

And sometimes, just sometimes, it actually makes sense to have a WHEN OTHERS. For example, if a program fails, under any circumstances, maybe you want it to log the exception or send an email to a support account.

But then, just sometimes, you want a program to FAIL, and to fail in such a way that even the most resilient of exception handlers won't catch it. Maybe you're working on a function that gets called way down the bottom of a deep call stack, and right there at the top is a WHEN OTHERS exception handler you can't afford to trigger.

PL/SQL doesn't support classes or sets or exception handlers. But there's one exception that is superspecial. Okay, NO_DATA_FOUND is special, as I've discussed before, but this one is special in a different way.

This is exception -0028, licenced to die. (Actually, the double-zero prefix is unnecessary, but it is still Christmas holidays for me, so I'm sneaking in a Bond reference).

This is a regular exception.


SQL> declare
  2    e_suicide exception;
  3    pragma exception_init(e_suicide,-27);
  4  begin
  5    raise e_suicide;
  6  exception
  7    when others then
  8      dbms_output.put_line('Caught:'||sqlcode);
  9  end;
 10  /
Caught:-27

PL/SQL procedure successfully completed.

And this is the uncatchable exception (oh, and Inskipp the uncatchable was a character from the Stainless Steel Rat)

SQL> declare

  2    e_suicide exception;

  3    pragma exception_init(e_suicide,-28);

  4  begin

  5    raise e_suicide;

  6  exception

  7    when others then

  8      dbms_output.put_line('Caught:'||sqlcode);

  9  end;

 10  /

declare

*

ERROR at line 1:

ORA-00028: your session has been killed

ORA-06512: at line 5

When it says the session has been killed, it means it. It is dead. An ex-session. Not pining for a PGA, but deceased and bereft of state.

SQL> select 1 from dual;

select 1 from dual

*

ERROR at line 1:

ORA-01012: not logged on

This one will return control to the calling program and a new connection will need to be established to the database if desired.