Oracle uses an exception handler from the first block in subsequent blocks

Question

I encounter a behavior in which I try to use case-specific exception handlers for several Oracle PL / SQL blocks in a Flyway script and Oracle, which apparently contradicts its documented scope 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;

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

Flowing around blocks in more blocks does not help.

What's going on here? How to stop this?

Answer 1

, , 11.2.0.4, 12.1.0.2 12.2.0.1. , DDL - ( null; , , - , ), , , if :

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

, 13, (), 18; 8, . ( at line 13 12.2, 11.2 12.1 ORA-06512, . , 12 2 , .)

, , . "", , , .

, if, , raise - - ; , :

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

:

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

. - .

dbms_output, - , , , (, begin execute immediate 'select * from dual'; end;, into...). null; .

, , , -.

, , , , Oracle. - , , - .