Transactions - Oracle Database Transactions and Locking Revealed - page 81

EODA@ORA12CR1> select * from t;
no rows selected

EODA@ORA12CR1> select * from t2;

CNT
----------
0

As you can see, Oracle treated the stored procedure call as an atomic statement. The client submitted a block of
code— BEGIN P; END;— and Oracle wrapped a SAVEPOINT around it. Since P failed, Oracle restored the database back
to the point right before it was called.

■ the preceding behavior—statement-level atomicity—relies on the pL/sQL routine not performing any commits
or rollbacks. it is my opinion that COMMIT and ROLLBACK should not be used in general in pL/sQL; the invoker of the
pL/sQL stored procedure is the only one that knows when a transaction is complete. it is a bad programming practice to
issue a COMMIT or ROLLBACK in your developed pL/sQL routines.

Note

Now, if we submit a slightly different block, we will get entirely different results:

EODA@ORA12CR1> begin
2 p;
3 exception
4 when others then
5 dbms_output.put_line( 'Error!!!! ' || sqlerrm );
6 end;
7 /
I fired and updated 1 rows
I fired and updated 1 rows
I fired and updated 1 rows
Error!!!! ORA-02290: check constraint (EODA.SYS_C0061484) violated
PL/SQL procedure successfully completed.

EODA@ORA12CR1> select * from t;

X
----------
1

EODA@ORA12CR1> select * from t2;

CNT
----------
1

EODA@ORA12CR1> rollback;
Rollback complete.