Pragma Autonomous Transaction Test Script

View All Scripts Login to Run Script

Script Name Pragma Autonomous Transaction Test Script
Description Simple example of how Pragma Autonomous Transaction works in Stored Procedure.
Area PL/SQL General
Contributor Manasi
Created Sunday June 04, 2017

Statement 1

CREATE TABLE t (  
test_value VARCHAR2(25))

Table created.

Statement 2

CREATE OR REPLACE PROCEDURE child_block IS 
 
BEGIN 
  INSERT INTO t 
  (test_value) 
  VALUES 
  ('Child block insert'); 
  COMMIT;  
END child_block;

Procedure created.

Statement 3

CREATE OR REPLACE PROCEDURE parent_block IS 
 
BEGIN 
  INSERT INTO t 
  (test_value) 
  VALUES 
  ('Parent block insert'); 
 
   child_block; 
 
   ROLLBACK;  
END parent_block;

Procedure created.

Statement 4
```
exec parent_block
```
```
Statement processed.
```

Statement 5

select * from t


TEST_VALUE
Parent block insert
Child block insert

2 rows selected.

TEST_VALUE
Parent block insert
Child block insert

Statement 6

CREATE OR REPLACE PROCEDURE child_block IS 
 
PRAGMA AUTONOMOUS_TRANSACTION; 
 
BEGIN 
  INSERT INTO t 
  (test_value) 
  VALUES 
  ('Child block insert'); 
 
  COMMIT; 
END child_block;

Procedure created.

Statement 7
```
TRUNCATE TABLE t
```
```
Table truncated.
```
Statement 8
```
exec parent_block
```
```
Statement processed.
```

Statement 9

select * from t


TEST_VALUE
Child block insert

TEST_VALUE
Child block insert

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database