Oracle 19c Why we can't loop through ORA-01013 exception (when user cancelled sql)

Question

I found some behavior that I don't understand and I can't find it in the documentation. Maybe one of you can explain to me what happened here?

What I want to achieve is to catch an exception when the user cancels the execution of the procedure. At the highest level of the application, in such a situation I want to perform a few additional procedures, including closing the logs, getting the content for the exception and throwing my own error.

Some of the functions that I want to run in the exception part have a "for loop" execution embedded in the code, and this is where the problem appears.

Example what I want and can't: https://sqlfiddle.com/oracle-plsql/online-compiler?id=ba5cfd92-e6f5-434b-b7af-c94ff874b471

declare
  e_sql_cancelled exception;
  pragma exception_init(e_sql_cancelled, -01013);
begin
  raise e_sql_cancelled;
exception
  when e_sql_cancelled then
    for a in ( select 'my value' as value from dual t ) loop null; end loop;
    raise_application_error(-20001, 'My error', false);
end;
/

This code is not able to reach the line with "raise exception 'My error'" because "for loop" causes error "ORA-06510: PL/SQL: unhandled user-defined exception"

ERROR at line 1: 
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 8 
ORA-06512: at line 8 
ORA-01013: User requested cancel of current operation. 
ORA-06512: at line 5

If someone comes here with the same problem, the solution is to replace the loops with functions that are run recursively, the effect is the same, only now I have to rewrite a lot of code (and probably another developer will add a loop somewhere and everything will fall apart again).

Example what I have to: https://sqlfiddle.com/oracle-plsql/online-compiler?id=38d92118-166c-4c66-b6d2-011f5162462b

declare
  e_sql_cancelled exception;
  pragma exception_init(e_sql_cancelled, -01013);
  
  l_varchar varchar2(4000);
  
  function fn_recursion(p_param in varchar2) return varchar2 is 
    l_return varchar2(4000);
  begin
    if length(p_param) <> 0 then
      l_return := fn_recursion(substr(p_param, 1, length(p_param)-1));
    end if;
    return l_return;
  end;
begin
  raise e_sql_cancelled;
exception
  when e_sql_cancelled then
    l_varchar := fn_recursion('12345678901234567890');
    raise_application_error(-20001, 'My error', false);
end;
/

Thanks to recursion, everything works as it should, but experience tells me that this is a solution that is not sustainable in the long term in a distributed team of developers.

ERROR at line 1:
ORA-20001: My error
ORA-06512: at line 20

The solution to the problem, although not fail-proof in the long term, but I have it.

I am looking for an answer: WHY?. What is going on here? Is there any documented use of the loop with this particular exception? Or maybe there is some other, better way to avoid this problem? If I can fire functions recursively, what's the difference whether I do it this way or using a loop?

Answer 1

You aren't supposed to try to raise Oracle's own predefined exceptions. By initializing your user-defined exception with Oracle's internal ORA-01013 (pragma exception_init(e_sql_cancelled, -01013);), you allow the convenient catching of the exception by name in your handler: (when e_sql_cancelled then). You are not, however, supposed to raise it yourself (raise e_sql_cancelled;). Oracle might permit this, but the behavior may not be what you expect: by throwing an ORA-01013 (SQL cancellation) your SQL in the handler is itself cancelled, which raises again, hence skipping your last line. It seems to affect cursor loops, though not single-row select into. That's interesting, but then you're doing something you're not supposed to be doing, so unexpected behavior should be expected.

Comment out the pragma and you'll see it works. Then you're throwing a normal user-defined exception with no weird side-effects.

Unfortunately, you cannot catch user cancellation in PL/SQL. When a user cancels (Cntrl-C), they cancel their current database call. If they are invoking PL/SQL, that call is the EXEC call on the PL/SQL itself. If that successfully cancels, the execution stops - how would a handler handle this if the handler itself is part of that execution? Handlers are not meant to catch this, any more than they can catch a session being killed. Those session-level events are above and outside the scope of the executing code.

You can catch a cancellation, but only using a system trigger of type after servererror on database. Those will fire even on SQL cancellation and session kills. But you'll need DBA-level privs to create such a trigger.

However, doing so would be quite atypical, which is typically a sign that we may be designing incorrectly. Rather than attempt to clean-up after a SQL cancellation (or session kill), write your code with proper transaction control in such a way that you never leave things in an inconsistent state in the first place. No cleanup is far better than after-the-fact cleanup.

If you are unable to do this (because it involves DDL, not just DML), then the next best option is cleanup by the next execution (perhaps you have a temp table that needs to be dropped - do that at the start of the program, cleaning up previous failures, rather than trying to do that at the tail of the failing execution). Even if you can catch SQL cancellations and normal session kills using a database trigger, you will never be able to catch a kill -9 on the OS pid since that terminates the running process at the OS level - no cleanup by that same process will ever be possible.