Mega Code Archive
Autonomous transactions
<![CDATA[
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 "RNTSOFT.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>
]]>