Transactions - Oracle Database Transactions and Locking Revealed - page 104

Now let's test it out. To make it interesting, we'll create a couple of procedures that will call each other:
EODA@ORA12CR1> create table t ( x int check (x>0) );
Table created.
EODA@ORA12CR1> create or replace procedure p1( p_n in number )
2 as
3 begin
4 -- some code here
5 insert into t (x) values ( p_n );
6 end;
7 /
Procedure created.
EODA@ORA12CR1> create or replace procedure p2( p_n in number )
2 as
3 begin
4 -- code
5 -- code
6 p1(p_n);
7 end;
8 /
Procedure created.
And then we'll invoke those routines from an anonymous block:
EODA@ORA12CR1> begin
2 p2( 1 );
3 p2( 2 );
4 p2( -1);
5 exception
6 when others
7 then
8 log_error( sqlerrm, dbms_utility.format_error_backtrace );
9 RAISE;
10 end;
11 /
begin
*
ERROR at line 1:
ORA-02290: check constraint (EODA.SYS_C0061527) violated
ORA-06512: at line 9
Now, we can see the code failed (you want that error returned, hence the RAISE on line 9). We can verify that
Oracle undid our work (we know that the first two calls to procedure P2 succeeded; the values 1 and 2 are successfully
inserted into our table T ):
EODA@ORA12CR1> select * from t;
no rows selected