Database Reference
In-Depth Information
How Autonomous Transactions Work
The best way to demonstrate the actions and consequences of an autonomous transaction is by example. We'll create
a simple table to hold a message:
EODA@ORA12CR1> create table t ( msg varchar2(25) );
Table created.
Next, we'll create two procedures, each of which simply
INSERT
s its name into the message table and commits.
However, one of these procedures is a normal procedure and the other is coded as an autonomous transaction.
We'll use these objects to show what work persists (is committed) in the database under various circumstances.
First, here's the
AUTONOMOUS_INSERT
procedure:
EODA@ORA12CR1> create or replace procedure Autonomous_Insert
2 as
3 pragma autonomous_transaction;
4 begin
5 insert into t values ( 'Autonomous Insert' );
6 commit;
7 end;
8 /
Procedure created.
Note the use of the pragma
AUTONOMOUS_TRANSACTION
. This directive tells the database that this procedure, when
executed, is to be executed as a new autonomous transaction, independent from its parent transaction.
■
a
pragma
is simply a compiler directive, a method to instruct the compiler to perform some compilation option.
other pragmas are available. refer to the
Oracle Database PL/SQL Language Reference
manual; you'll find a list of them
in its index.
Note
And here's the “normal”
NONAUTONOMOUS_INSERT
procedure:
EODA@ORA12CR1> create or replace procedure NonAutonomous_Insert
2 as
3 begin
4 insert into t values ( 'NonAutonomous Insert' );
5 commit;
6 end;
7 /
Procedure created.
Now let's observe the behavior of the
nonautonomous
transaction in an anonymous block of PL/SQL code:
EODA@ORA12CR1> begin
2 insert into t values ( 'Anonymous Block' );
3 NonAutonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed.
Search WWH ::
Custom Search