CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 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 [140 users online]    © 2010 psoug.org