Oracle PL/SQL/PL SQL/Transaction

Autonomous transactions.

   <source lang="sql">

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>

</source>
   
  

Calling an autonomous function from SQL.

   <source lang="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>

</source>
   
  

Commit an insert statement in PL SQL

   <source lang="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> --

</source>
   
  

interaction between savepoints and autonomous transactions.

   <source lang="sql">

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>

</source>
   
  

set transaction use rollback segment

   <source lang="sql">

 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> --

</source>
   
  

The pragma is legal in top-level anonymous blocks:

   <source lang="sql">

SQL> DECLARE

 2    PRAGMA AUTONOMOUS_TRANSACTION;
 3  BEGIN
 4    COMMIT;
 5  END;
 6  /

PL/SQL procedure successfully completed. SQL> SQL> SQL>

</source>
   
  

The pragma is not legal in nested blocks:

   <source lang="sql">

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>

</source>
   
  

The pragma is valid in a packaged procedure.

   <source lang="sql">

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>

</source>
   
  

The pragma is valid in both standalone and local subprograms.

   <source lang="sql">

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>

</source>