Oracle Pragma Autonomous Transaction

Oracle Pragma Autonomous Transaction Version 11.2.0.3

General Information

Data Dictionary Objects

ALL_SOURCE	SOURCE$	USER_SOURCE
USER_SOURCEDBA_SOURCE

Objects Utilizing Autonomous Transactions

-- code can only be identified as being an autonomous transaction by reviewing its source

conn / as sysdba

SELECT DISTINCT owner, name
FROM dba_source
WHERE UPPER(text) LIKE '%AUTONOMOUS_TRANSACTION%'
ORDER BY 1, 2;

Related Privileges

CREATE PROCEDURE

conn / as sysdba

GRANT create procedure TO uwclass;

Autonomous Transaction Demo 1

Without Pragma Autonomous Transaction

conn uwclass/uwclass

CREATE TABLE t (
test_value VARCHAR2(25));

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

CREATE OR REPLACE PROCEDURE parent_block AUTHID CURRENT_USER IS
BEGIN
INSERT INTO t
(test_value)
VALUES
('Parent block insert');

child_block;

ROLLBACK;
END parent_block;
/

-- run the parent procedure
exec parent_block

-- check the results
SELECT * FROM t;

With Pragma Autonomous Transaction

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

COMMIT;
END child_block;
/

-- empty the test table
TRUNCATE TABLE t;

-- run the parent procedure
exec parent_block;

-- check the results
SELECT * FROM t;

Autonomous Transaction Demo 2

Without Pragma Autonomous Transaction

CREATE TABLE t (testcol NUMBER);

CREATE OR REPLACE FUNCTION howmanyrows RETURN INTEGER AUTHID CURRENT_USER IS
i INTEGER;
BEGIN
SELECT COUNT(*)
INTO i
FROM t;

RETURN i;
END howmanyrows;
/

CREATE OR REPLACE PROCEDURE testproc AUTHID CURRENT_USER IS
a INTEGER;
b INTEGER;
c INTEGER;
BEGIN
SELECT COUNT(*)
INTO a
FROM t;

INSERT INTO t VALUES (1);
COMMIT;

INSERT INTO t VALUES (2);
INSERT INTO t VALUES (3);

b := howmanyrows;

INSERT INTO t VALUES (4);
INSERT INTO t VALUES (5);
INSERT INTO t VALUES (6);
COMMIT;

SELECT COUNT(*)
INTO c
FROM t;

dbms_output.put_line(a);
dbms_output.put_line(b);
dbms_output.put_line(c);
END testproc;
/

set serveroutput on

exec testproc

With Pragma Autonomous Transaction

CREATE OR REPLACE FUNCTION howmanyrows AUTHID DEFINER RETURN INTEGER IS
i INTEGER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT COUNT(*)
INTO i
FROM t;

RETURN i;
END howmanyrows;
/

exec testproc