Oracle PL/SQL/PL SQL/Transaction

Autonomous transactions.

 
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE Autonomous AS
  2    PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4    INSERT INTO MyTable VALUES (-10, "Hello from Autonomous!");
  5    COMMIT;
  6  END Autonomous;
  7  /
Procedure created.
SQL>
SQL> BEGIN
  2    INSERT INTO MyTable VALUES (-10, "Hello from the parent!");
  3
  4    Autonomous;
  5
  6    ROLLBACK;
  7  END;
  8  /
BEGIN
*
ERROR at line 1:
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "sqle.AUTONOMOUS", line 5
ORA-06512: at line 4

SQL>
SQL> SELECT * FROM MyTable WHERE num_col = -10;
no rows selected
SQL>
SQL> DECLARE
  2    PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4    INSERT INTO MyTable (num_col) VALUES (1);
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at line 5

SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>

Calling an autonomous function from SQL.

 
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL> CREATE OR REPLACE FUNCTION LogParam(p1 IN NUMBER)
  2    RETURN NUMBER AS
  3    PRAGMA AUTONOMOUS_TRANSACTION;
  4  BEGIN
  5    INSERT INTO MyTable (num_col, char_col)
  6      VALUES (p1, "Logged!");
  7    COMMIT;
  8    RETURN p1;
  9  END LogParam;
 10  /
Function created.
SQL>
SQL> SELECT LogParam(1) FROM dual;
SELECT LogParam(1) FROM dual
       *
ERROR at line 1:
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "sqle.LOGPARAM", line 7

SQL>
SQL> SELECT num_col
  2    FROM MyTable
  3    WHERE char_col = "Logged!";
no rows selected
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>

Commit an insert statement in PL SQL

 
SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL>
SQL> begin
  2          for i in 1 .. 100000
  3          loop
  4                  insert into t values ( i );
  5                  commit ;
  6          end loop;
  7  end;
  8  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL>
SQL> --

interaction between savepoints and autonomous transactions.

 
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE AutoProc AS
  2    PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4    ROLLBACK TO SAVEPOINT A;
  5  END AutoProc;
  6  /
Procedure created.
SQL>
SQL> BEGIN
  2    SAVEPOINT A;
  3    INSERT INTO MyTable (char_col)
  4      VALUES ("Savepoint A!");
  5    AutoProc;
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-00034: cannot ROLLBACK in current PL/SQL session
ORA-06512: at "sqle.AUTOPROC", line 4
ORA-06512: at line 5

SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>

set transaction use rollback segment

 
  2
SQL> create table t
  2  as
  3  select * from all_objects;
Table created.
SQL>
SQL>
SQL> set echo on
SQL> drop table done;
Table dropped.
SQL> create table done( object_id int );
Table created.
SQL> insert into done values ( 0 );
1 row created.
SQL>
SQL> declare
  2          l_cnt number;
  3          l_max number;
  4  begin
  5          select object_id into l_cnt from done;
  6          select max(object_id) into l_max from t;
  7
  8          while ( l_cnt < l_max )
  9          loop
 10                  update t
 11                     set object_name = lower(object_name)
 12                   where object_id > l_cnt
 13                     and object_id <= l_cnt+100;
 14
 15                  update done set object_id = object_id+100;
 16
 17                  commit;
 18                  set transaction use rollback segment rbs_small;
 19                  l_cnt := l_cnt + 100;
 20          end loop;
 21  end;
 22  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --

The pragma is legal in top-level anonymous blocks:

 
SQL> DECLARE
  2    PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4    COMMIT;
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

The pragma is not legal in nested blocks:

 
SQL> BEGIN
  2    DECLARE
  3      PRAGMA AUTONOMOUS_TRANSACTION;
  4    BEGIN
  5      COMMIT;
  6    END;
  7  END;
  8  /
    PRAGMA AUTONOMOUS_TRANSACTION;
           *
ERROR at line 3:
ORA-06550: line 3, column 12:
PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be specified here

SQL>
SQL>
SQL>

The pragma is valid in a packaged procedure.

 
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE Auto2 AS
  2    PROCEDURE P;
  3  END Auto2;
  4  /
Package created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY Auto2 AS
  2    PROCEDURE P IS
  3      PRAGMA AUTONOMOUS_TRANSACTION;
  4    BEGIN
  5      COMMIT;
  6    END P;
  7  END Auto2;
  8  /
Package body created.
SQL> show errors
No errors.
SQL>

The pragma is valid in both standalone and local subprograms.

 
SQL>
SQL> CREATE OR REPLACE PROCEDURE Auto1 AS
  2    PRAGMA AUTONOMOUS_TRANSACTION;
  3
  4    PROCEDURE Local IS
  5      PRAGMA AUTONOMOUS_TRANSACTION;
  6    BEGIN
  7      ROLLBACK;
  8    END Local;
  9  BEGIN
 10    Local;
 11    COMMIT;
 12  END Auto1;
 13  /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL>