Autonomous transactions. : Transaction « PL SQL « Oracle PL / SQL






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 "JAVA2S.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>

 








Related examples in the same category

1.Commit an insert statement in PL SQL
2.set transaction use rollback segment
3.The pragma is legal in top-level anonymous blocks:
4.The pragma is not legal in nested blocks:
5.The pragma is valid in both standalone and local subprograms.
6.The pragma is valid in a packaged procedure.
7.interaction between savepoints and autonomous transactions.
8.Calling an autonomous function from SQL.