in foo_proc
Static call - 1
in foo_proc
NDS - 0
in foo_proc
dbms_sql - 0

create table test$tab (val varchar2(1));

create or replace  procedure foo_proc is
begin
  dbms_output.put_line('in foo_proc');
  insert into test$tab values ('t');
  --raise no_data_found;
  raise_application_error(-20001,'Error');
end;
/

DECLARE
  stmt VARCHAR2(200);
  c    NUMBER;
  i    NUMBER;

  FUNCTION count_rows RETURN NUMBER IS
    l_cnt NUMBER;
  BEGIN
    SELECT COUNT(*) INTO l_cnt FROM test$tab t;
  
    RETURN l_cnt;
  END;

BEGIN
  
  DELETE FROM test$tab;
  BEGIN
    foo_proc;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Static call - ' || count_rows);
  END;

  DELETE FROM test$tab;
  BEGIN
    EXECUTE IMMEDIATE 'begin foo_proc; end;';
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('NDS - ' || count_rows);
  END;

  DELETE FROM test$tab;
  BEGIN
    stmt := 'begin foo_proc; end;';
  
    i := dbms_sql.open_cursor;
    dbms_sql.parse(c => i, STATEMENT => stmt, language_flag => dbms_sql.native);
    c := dbms_sql.execute(i);
    dbms_sql.close_cursor(i);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('dbms_sql - ' || count_rows);
      IF dbms_sql.is_open(i)
      THEN
        dbms_sql.close_cursor(i);
      END IF;
  END;
END;
/

drop procedure foo_proc;
drop table test$tab;

I hope you dont mind I'll use this to blog about because *that* is an excellent test case! I just cut/paste into my SQL Plus and the problem is described perfectly.

If only all asktom questions were posed this way. Thanks !

Anyway...onto your question. This is working as designed, and is documented in the PLSQL manual, just perhaps not as explicitly as it could be:

From http://docs.oracle.com/database/121/LNPLS/errors.htm#LNPLS850

"If a stored subprogram exits with an unhandled exception, PL/SQL does not roll back database changes made by the subprogram."

So that takes care of your first test case - you called a subprogram, we did the change and did not roll it back.

The last two tests are just examples of standard statement level atomicity, ie, a statement works in its entirity or it doesnt. In each case, you have executed a single "SQL" statement, which failed, so we roll back the statement.

And just to clarify further, if you remove the exception handler from test case #1, ie, so that the error is returned to the calling environment, then it is the anonymous block itself that become the single statement, and hence the change is rollback

SQL> set serverout on
SQL> DECLARE
  2    stmt VARCHAR2(200);
  3    c    NUMBER;
  4    i    NUMBER;
  5
  6    FUNCTION count_rows RETURN NUMBER IS
  7      l_cnt NUMBER;
  8    BEGIN
  9      SELECT COUNT(*) INTO l_cnt FROM test$tab t;
 10
 11      RETURN l_cnt;
 12    END;
 13
 14  BEGIN
 15
 16    DELETE FROM test$tab;
 17    commit;
 18    BEGIN
 19      foo_proc;
 20  --  EXCEPTION
 21  --    WHEN OTHERS THEN
 22  --      dbms_output.put_line('Static call - ' || count_rows);
 23    END;
 24
 25    DELETE FROM test$tab;
 26    commit;
 27    BEGIN
 28      EXECUTE IMMEDIATE 'begin foo_proc; end;';
 29    EXCEPTION
 30      WHEN OTHERS THEN
 31        dbms_output.put_line('NDS - ' || count_rows);
 32    END;
 33
 34    DELETE FROM test$tab;
 35    commit;
 36    BEGIN
 37      stmt := 'begin foo_proc; end;';
 38
 39      i := dbms_sql.open_cursor;
 40      dbms_sql.parse(c => i, STATEMENT => stmt, language_flag => dbms_sql.native);
 41      c := dbms_sql.execute(i);
 42      dbms_sql.close_cursor(i);
 43    EXCEPTION
 44      WHEN OTHERS THEN
 45        dbms_output.put_line('dbms_sql - ' || count_rows);
 46        IF dbms_sql.is_open(i)
 47        THEN
 48          dbms_sql.close_cursor(i);
 49        END IF;
 50    END;
 51  END;
 52  /
in foo_proc
DECLARE
*
ERROR at line 1:
ORA-20001: Error
ORA-06512: at "MCDONAC.FOO_PROC", line 5
ORA-06512: at line 19


SQL>
SQL> SELECT COUNT(*) FROM test$tab t;

  COUNT(*)
----------
         0

1 row selected.