interaction between savepoints and autonomous transactions. : Transaction « PL SQL « Oracle PL / SQL






  1. Oracle PL / SQL
  2. PL SQL
  3. Transaction

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 "JAVA2S.AUTOPROC", line 4
ORA-06512: at line 5


SQL>
SQL>
SQL> drop table MyTable;

Table dropped.

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.Calling an autonomous function from SQL.
8.Autonomous transactions.