Oracle Pragma Autonomous Transaction
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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
CREATE TRIGGER
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
Database Security
Pipelined Table Functions
Pragmas
Procedures
SUPPRESS_WARNING_6009
Table Triggers
What's New In 21c
What's New In 23c

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