Database Reference
In-Depth Information
The “magic” of this routine is on line 5 where we used the
pragma autonomous_transaction
directive to inform
PL/SQL that we want this subroutine to start a new transaction, perform some work in it, and commit it—without
affecting any other transaction currently in process. The
COMMIT
on line 9 can affect only the SQL performed by this
LOG_ERROR
procedure.
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
