Oracle PL/SQL – Exceptions and Native Dynamic SQL

EXECUTE IMMEDIATE statements act as separate sessions, so it is important to note that exception propagation from these statements also follows the same model. When an exception propagates beyond an EXECUTE IMMEDIATE entry point, a rollback is incurred. This may come as a surprise since normal procedure calls do not rollback, leaving transaction control in the hands of the programmer. Here is an example to demonstrate. First, some support objects:

SQL> CREATE TABLE test_tab
  2  (
  3    id NUMBER
  4  );

Table created.


SQL> CREATE OR REPLACE
  2  PROCEDURE error_proc
  3  IS
  4  BEGIN
  5
  6    INSERT INTO test_tab ( id )
  7    VALUES ( 1 );
  8
  9    RAISE_APPLICATION_ERROR( -20001, 'BOOM!' );
 10
 11  END;
 12  /

Procedure created.

Now, an example of exception propagation during a normal procedure call. Notice that, although the exception is thrown, the executed DML remains intact.

SQL> SELECT *
  2  FROM test_tab;

no rows selected


SQL> BEGIN
  2
  3    error_proc;
  4
  5  EXCEPTION
  6    WHEN OTHERS THEN
  7      NULL;
  8  END;
  9  /

PL/SQL procedure successfully completed.


SQL> SELECT *
  2  FROM test_tab;

        ID
----------
         1


SQL> ROLLBACK;

Rollback complete.

Finally, an almost identical example using Dynamic SQL. Notice that this time the DML is lost to a rollback incurred by the EXECUTE IMMEDIATE.

SQL> SELECT *
  2  FROM test_tab;

no rows selected


SQL> BEGIN
  2
  3    EXECUTE IMMEDIATE 'BEGIN error_proc; END';
  4
  5  EXCEPTION
  6    WHEN OTHERS THEN
  7      NULL;
  8  END;
  9  /

PL/SQL procedure successfully completed.


SQL> SELECT *
  2  FROM test_tab;

no rows selected
Advertisements