Oracle Pragma Autonomous Transaction
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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
What's New In 12cR2
What's New In 18cR3

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-2017 Daniel A. Morgan All Rights Reserved