Oracle Pragma Autonomous Transaction
Version 12.1.0.2

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
Data Dictionary Objects
ALL_SOURCE_AE DBA_SOURCE_AE USER_SOURCE_AE
CDB_SOURCE_AE 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

Related Topics
Functions
Packages
Pipelined Table Functions
Pragmas
Procedures
Table Triggers

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved