Oracle Autonomous Transactions
Version 11.2
 
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
 
 
Morgan's Library Page Footer
This is site maintained by Dan Morgan. Last Updated: Contact Us Legal Notices & Terms of Use  Privacy Statement