oracle - How to propagate globally defined exception through execute immediate? -

here minimal test case reason fails

ora-06510: pl/sql: unhandled user-defined exception

create package my_test   global_exception exception; end; /  set serveroutput on;  begin   execute immediate 'begin raise my_test.global_exception; end;'; exception   when my_test.global_exception      dbms_output.put_line('global_exception'); end; / 

here test case works:

begin   raise my_test.global_exception; exception   when my_test.global_exception      dbms_output.put_line('global_exception'); end; / 

is there way raise global exceptions through execute immediate? database version 12c or 11g

you can catch if use dbms_sql instead of execute immediate (in 11gr2):

declare   l_cur pls_integer;   l_rc pls_integer; begin   l_cur := dbms_sql.open_cursor;   dbms_sql.parse (l_cur, 'begin raise my_test.global_exception; end;', dbms_sql.native);   l_rc := dbms_sql.execute(l_cur); exception   when my_test.global_exception      dbms_output.put_line('global_exception'); end; /  pl/sql procedure completed.  global_exception 

not entirely sure why works yours doesn't though.

ah, investigating different behaviour threw hint. can catch execute immediate if associate error number exception in package:

create package my_test   global_exception exception;   pragma exception_init(global_exception, -20001); end; /  begin   execute immediate 'begin raise my_test.global_exception; end;'; exception   when my_test.global_exception      dbms_output.put_line('global_exception'); end; /  pl/sql procedure completed.  global_exception 

you can raise exception statically in anonymous block too, though isn't useful; without pragma gets ora-06510, doing within execute immediate:

begin   raise my_test.global_exception; end; /  error report - ora-20001:  ora-06512: @ line 2 

that ora-20001 can thrown inside execute immediate , pragma allows recognised through context switch. or that. dbms_sql package handles differently, seems.