Oracle Autonomous Transaction PRAGMA


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Search the Reference Library pages:

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Autonomous Transactions

Version 11.1

General

Related Data Dictionary Objects

source$

dba_source

all_source

user_source

Related Data Dictionary Objects

-- Code can only be identified as being an autonomous transaction by reviewing the source code:

conn / as sysdba

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

Related Privileges

CREATE PROCEDURE

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 IS

BEGIN
INSERT INTO t
(test_value)
VALUES
('Child block insert');
COMMIT;
END child_block;
/

CREATE OR REPLACE PROCEDURE parent_block 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 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 IS
i INTEGER;
BEGIN
SELECT COUNT(*)
INTO i
FROM t;

RETURN i;
END howmanyrows;
/

CREATE OR REPLACE PROCEDURE testproc 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 RETURN INTEGER IS
i INTEGER;

PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT COUNT(*)
INTO i
FROM t;

RETURN i;
END howmanyrows;
/

exec testproc

Related Topics

Functions

Packages

Pipelined Table Functions

Pragmas

Procedures

Table Triggers

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us