How to propagate globally defined exception through execute immediate?

Question

Here is a minimal test case that for some reason fails with

ORA-06510: PL/SQL: unhandled user-defined exception

CREATE PACKAGE my_test
AS
  global_exception EXCEPTION;
END;
/

set serveroutput on;

BEGIN
  execute immediate 'BEGIN RAISE my_test.global_exception; END;';
EXCEPTION
  WHEN my_test.global_exception THEN 
    dbms_output.put_line('global_exception');
END;
/

Here is a test case that works:

BEGIN
  RAISE my_test.global_exception;
EXCEPTION
  WHEN my_test.global_exception THEN 
    dbms_output.put_line('global_exception');
END;
/

Is there a way to raise global exceptions through EXECUTE IMMEDIATE? Database version 12c or 11g

Alex Poole · Accepted Answer · 2016-06-10T16:01:17.353

You can catch it if you 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 THEN 
    dbms_output.put_line('global_exception');
END;
/

PL/SQL procedure successfully completed.

global_exception

Not entirely sure why that works but yours doesn't though.

Ah, investigating the different behaviour threw up a hint. You can catch it with execute immediate if you associate an error number with the exception in your package:

CREATE PACKAGE my_test
AS
  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 THEN 
    dbms_output.put_line('global_exception');
END;
/

PL/SQL procedure successfully completed.

global_exception

You can now raise the exception statically in an anonymous block too, though that isn't very useful; without the pragma this also gets ORA-06510, as it is what you were doing within that execute immediate:

BEGIN
  RAISE my_test.global_exception;
END;
/

Error report -
ORA-20001: 
ORA-06512: at line 2

That ORA-20001 can now be thrown inside the execute immediate and the pragma allows it to be recognised through the context switch. Or something like that. The dbms_sql package handles it slightly differently, it seems.

How to propagate globally defined exception through execute immediate?

1 Answers1