Transactions - Oracle Database Transactions and Locking Revealed - page 80

In Oracle, this statement-level atomicity extends as deep as it needs to. In the preceding example, if the INSERT
INTO T fires a trigger that updates another table, and that table has a trigger that deletes from another table (and so
on, and so on), either all of the work succeeds or none of it does. You don't need to code anything special to ensure
this; it's just the way it works.
Procedure-Level Atomicity
It is interesting to note that Oracle considers PL/SQL blocks to be statements as well. Consider the following stored
procedure and reset of the example tables:
EODA@ORA12CR1> create or replace procedure p
2 as
3 begin
4 insert into t values ( 1 );
5 insert into t values (-1 );
6 end;
7 /
Procedure created.
EODA@ORA12CR1> delete from t;
0 rows deleted.
EODA@ORA12CR1> update t2 set cnt = 0;
1 row updated.
EODA@ORA12CR1> commit;
Commit complete.
EODA@ORA12CR1> select * from t;
no rows selected
EODA@ORA12CR1> select * from t2;
CNT
----------
0
So, we have a procedure we know will fail, and the second INSERT will always fail in this case. Let's see what
happens if we run that stored procedure:
EODA@ORA12CR1> begin
2 p;
3 end;
4 /
I fired and updated 1 rows
I fired and updated 1 rows
begin
*
ERROR at line 1:
ORA-02290: check constraint (EODA.SYS_C0061484) violated
ORA-06512: at "EODA.P", line 5
ORA-06512: at line 2