What’s obvious from reading the Oracle Concepts and PL/SQL Developers Guide is that Oracle sets an implicit savepoint before each single SQL statement and in case the statement fails (for whatever reason) Oracle rolls back to that savepoint. That’s what’s called “statement level rollback”. Apparently, for stored procedures that are called from a client the same rules apply. Before the procedure call a savepoint is set and if that procedure raises/propagates an exception Oracle rolls back to the implicit savepoint set just before the procedure call. It is fundamental to understand this behaviour as a Oracle database developer, especially when it comes to exception handling. Let’s examine the implications. First we create the table with index and constraint:

SQL> create table t1 (id number, text varchar2(50));
Table created.

SQL> create unique index t1_idx_01 on t1 (text);
Index created.

SQL> alter table t1 add constraint t1_uk_01 unique (text) using index t1_idx_01;
Table altered.

Then a stored procedure to insert some data into table T1:

SQL> create or replace procedure insert_t1
is
begin
  dbms_output.put_line('inserting first row');
  insert into t1 (id, text) values (1, 'first');

  dbms_output.put_line('inserting first row');
  insert into t1 (id, text) values (2, 'first'); -- this will rais ORA-00001

  dbms_output.put_line('inserting third row');
  insert into t1 (id, text) values (3, 'third'); -- this statement will not be executed due to previous exception
end insert_t1;
/
Procedure created.

This procedure tries to insert three records into table T1 but due to the unique constraint on TEXT the second insert statement will fail and the procedure returns with an exception propagated to the caller and subsequently everything done within the procedure will be rolled back.
Let’s put that to a test:

SQL> truncate table t1;
Table truncated.

SQL> exec insert_t1
BEGIN insert_t1; END;
*
ERROR at line 1:
ORA-00001: unique constraint (TESTUSER.T1_UK_01) violated
ORA-06512: at "TESTUSER.INSERT_T1", line 8
ORA-06512: at line 1

SQL> select * from t1;
no rows selected

So far so good, now let’s see what happens when we trap ORA-00001 (PL/SQL named exception DUP_VAL_ON_INDEX) and swallow it, not propagating the exception to the top-level call.

SQL> create or replace procedure insert_t1
is
begin
  dbms_output.put_line('inserting first row');
  insert into t1 (id, text) values (1, 'first');

  dbms_output.put_line('inserting first row');
  insert into t1 (id, text) values (2, 'first'); -- this will rais ORA-00001

  dbms_output.put_line('inserting third row');
  insert into t1 (id, text) values (3, 'third'); -- this statement will not be executed due to previous exception
exception
when dup_val_on_index then
  null;
end insert_t1;
/
Procedure created.

This time the procedures again tries to insert three records into table T1 but fails badly with the second record. The exception is trapped and silently swallowed so the procedure returns without an error, no exception propagation and therefore leaving one record in the table.

SQL> truncate table t1;
Table truncated.

SQL> exec insert_t1
PL/SQL procedure successfully completed.

SQL> select * from t1;
ID                  TEXT
---------- --------------------------------------------------
1                   first

1 row selected.

Ooops, something went wrong and we didn’t notice, if we proceed with the program flow in the application and eventually commit the transaction we leave the database in a inconsistent state as we expect three records to be present and not only one.

Alright, next time just let’s log the error and propagate it:

SQL> create or replace procedure insert_t1
is
begin
  dbms_output.put_line('inserting first row');
  insert into t1 (id, text) values (1, 'first');

  dbms_output.put_line('inserting first row');
  insert into t1 (id, text) values (2, 'first'); -- this will rais ORA-00001

  dbms_output.put_line('inserting third row');
  insert into t1 (id, text) values (3, 'third'); -- this statement will not be executed due to previous exception
exception
when dup_val_on_index then
  dbms_output.put_line('Error: duplicate records');
  raise_application_error(-20001, 'There can only be one "first"!');
end insert_t1;
/
Procedure created.

There we go, again whatever the application does we ensure the consistency of the data within the database.

SQL> truncate table t1;
Table truncated.

SQL> exec insert_t1
BEGIN insert_t1; END;
*
ERROR at line 1:
ORA-20001: There can only be one "first"!
ORA-06512: at "TESTUSER.INSERT_T1", line 15
ORA-06512: at line 1

SQL> select * from t1;
no rows selected

The propagation of exceptions can also be observed in a SQL trace where for a cursor “begin insert_t1; end;” with depth 0 a ERROR is following the EXEC call. If the error is trapped and ignored there is only a ERROR following the EXEC call of the the cursor “INSERT INTO T1(ID, TEXT) VALUES (2, ‘first’)” with depth 1 and no ERROR for the parent cursor.

Conclusion:
Be careful with stored procedures that trap exceptions and do not propagate them as this kind of breaks the statement level rollback that Oracle would perform when the exceptions are not caught at all or are being re-raised.

Foot note 1: in this text “stored procedure” or “procedure” represents any named PL/SQL construct (except triggers)