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

1. Oracle PL / SQL
2. PL SQL
3. 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 "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