Oracle uses exception handler from first block in later blocks

Question

I'm running into a behavior where I'm trying to use case-specific exception handlers for several Oracle PL/SQL blocks in a Flyway script and Oracle, apparently contradicting its documented scoping for exception handlers, sends all exceptions to the exception handler for the first block. For example, in this code:

begin

  begin
    execute immediate '
create table "test" (
  "id" number not null,
  "name" varchar2(100) not null,
  constraint "test_pk" primary key ("id")
)
';
  exception
  when others then
    if sqlcode != -955 then raise; end if;
  end;

  begin
    execute immediate 'fail to create index "test_name_idx" on "test" ("name")';
  exception
  when others then
    if sqlcode != -6512 then raise; end if;
  end;

end;

the ORA-06512 exception is not caught, and the exception raised is tagged as from line 13.

Wrapping the blocks in more blocks doesn't help.

What is going on here? How do I stop this from happening?

That’s very.... interesting. If you add a simple `dbms_output` call before the first raise (inside the if) it reports the correct line number, from a quick test on livesql.oracle.com. But also with more debugs, it doesn’t actually hit that first exception handler (as you’d expect of course), even when it reports the wrong line number. Looks like a (parsing?) bug, maybe in `dbms_sql`? Might be interesting to see if it behaves the same indifferent versions. — Alex Poole, Dec 08 '17 at 00:09
Looks a bit like bug 8856896 from 10g, but this is 12cR2, at least at that site. Might be time to raise a service request with Oracle support? — Alex Poole, Dec 08 '17 at 00:15
FWIW, reproduced problem on Oracle 12.1.0.2.0. Just an aside, wondering why you're trying to catch ORA-06512 ("error is on line n") since the actual exceptions I'd think you'd be interested in would be ORA-00955 ("name is already used by an existing object") and ORA-01408 ("such column list already indexed"). — Jeffrey Kemp, Dec 08 '17 at 05:20

score 3 · Accepted Answer · answered Dec 08 '17 at 09:46

This seems to be a bug, which has (so far) been reproduced in 11.2.0.4, 12.1.0.2 and 12.2.0.1. It doesn't seem to require DDL, or any real action in the first sub-block (though just doing null; as a placeholder doesn't trigger it, possibly because the compiler removes it), but it does seem to need the if inside both exception handlers:

begin
  begin
    dbms_output.put_line('Dummy message');
  exception
    when others then
      dbms_output.put_line('In first exception handler');
      if 1=1 then
        raise;
      end if;
  end;

  begin
    execute immediate 'invalid';
  exception
    when others then
      dbms_output.put_line('In second exception handler');
      if 1=1 then
        raise;
      end if;
  end;
end;
/

Dummy message
In second exception handler

ORA-00900: invalid SQL statement
ORA-06512: at line 8
ORA-06512: at line 13

As with your example the exception is thrown by line 13 so should be reported as (re-)raised at line 18; but it's instead it's reported as raised from line 8, which doesn't make sense. (The at line 13 message is only shown in 12.2; in 11.2 and 12.1 it only reports the first ORA-06512, which is rather more confusing. At least in 12 2 you have some clue where the problem really is.)

From the debugs you can see it doesn't actually use the first exception handler, and it does go into the second one. It 'only' seems to be reporting against the wrong line number, rather than executing the wrong code.

It appears that doing real work inside the if, immediately before the raise somehow fixes things - in either exception handling section; this adds a message in the first, which can't be reached:

begin
  begin
    dbms_output.put_line('Dummy message');
  exception
    when others then
      dbms_output.put_line('In first exception handler');
      if 1=1 then
        dbms_output.put_line('This avoids the bug somehow');
        raise;
      end if;
  end;

  begin
    execute immediate 'invalid';
  exception
    when others then
      dbms_output.put_line('In second exception handler');
      if 1=1 then
        raise;
      end if;
  end;
end;
/

Dummy message
In second exception handler

ORA-00900: invalid SQL statement
ORA-06512: at line 19
ORA-06512: at line 14

and this in the second:

begin
  begin
    dbms_output.put_line('Dummy message');
  exception
    when others then
      dbms_output.put_line('In first exception handler');
      if 1=1 then
        raise;
      end if;
  end;

  begin
    execute immediate 'invalid';
  exception
    when others then
      dbms_output.put_line('In second exception handler');
      if 1=1 then
        dbms_output.put_line('This avoids the bug somehow');
        raise;
      end if;
  end;
end;
/

Dummy message
In second exception handler

ORA-00900: invalid SQL statement
ORA-06512: at line 19
ORA-06512: at line 13

In both cases the reported line number is now correct. Somehow.

It doesn't have to be a dbms_output call, anything seems to work, such as a dummy procedure call or query, even an extra sub-block (e.g. begin execute immediate 'select * from dual'; end;, even though the query isn't executed because there's no into...). Again just using null; doesn't work though.

This is a bit ugly but gives you a way to stop it from happening at least, sort of.

It's clearly weird and unexpected and inconsistent behaviour, and has been around for a while, so it should probably be raised as a service request through My Oracle Support. I can't see any existing reports but I didn't look very hard so there might be one lurking somewhere.

Oracle uses exception handler from first block in later blocks

1 Answers1