CODEWITHSUNDEEP
oracle-databaseexceptionplsqlrollback
freesoft
freesoft

Reputation: 1144

PL/SQL: Contradiction in Oracle document on Implicit Rollbacks

In the document Oracle Database PL/SQL Language Reference 11g Release 2 (11.2), the "Implicit Rollbacks" section begins with this text:

"Before running an INSERT, UPDATE, DELETE, or MERGE statement, the database marks an implicit savepoint (unavailable to you). If the statement fails, the database rolls back to the savepoint. Usually, just the failed SQL statement is rolled back, not the whole transaction."

So if I run a SQL sentence into an PL/SQL program, and the sentence fails, then the sentence will be automatically rolled-back. That is ok.

But the same section ends with this text:

"If you exit a stored subprogram with an unhandled exception, PL/SQL does not assign values to OUT parameters, and does not do any rollback."

It seems to be contrary to the first text: No rollback is done if my program ends with an untrapped exception. But the first text says that the rollback is done automatically if the SQL sentence fails.

So if my stored program contains a SQL sentence, the sentence fails, the exception is not trapped, and my program ends, then should the SQL sentence be rolled-back or not? Does the document have a contradiction?

Related questions in Stack Overflow:

Update (solved): Thank DrabJay for the example, it is clearer now:

The rollback of a SQL statement that fails is ALWAYS done (independently of being into a program or not). The rollback of the program depends of the caller:

If the program is an anonymous block (no caller exists), it is equivalent to being called from a user statement, and a user statement that fails is rolled back automatically, so the anonymous block is rolled back.

I think the document should be clearer, specially on the words "and does not do any rollback":

"If you exit a stored subprogram with an unhandled exception, PL/SQL does not assign values to OUT parameters, and does not do any rollback."

Upvotes: 2

Views: 1171

Answers (1)

DrabJay
DrabJay

Reputation: 3099

There is no contradiction, but the documentation must be read precisely e,.g.

CREATE TABLE t
  (col NUMBER(1) NOT NULL)
/

Table created.

CREATE PROCEDURE insert_t1
AS
BEGIN
  INSERT INTO t
    (col)
  SELECT 1 FROM dual
  UNION ALL
  SELECT 2 FROM dual;
  INSERT INTO t
    (col)
  SELECT 9 FROM dual
  UNION ALL
  SELECT 10 FROM dual;
END;
/

Procedure created.

SELECT col
FROM t
/

no rows selected.

INSERT INTO t
SELECT 9 FROM dual
UNION ALL
SELECT 10 FROM dual
/

INSERT INTO t
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SELECT col
FROM t
/

no rows selected.

This shows, assuming that the attempt to insert the two records is in the order specified, that a DML statement rolls back to the implicit savepoint established before executing the statement, as neither record exists in the database. If we then continue:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  CURSOR csr
  IS
    SELECT col
    FROM t
    ORDER BY col;
BEGIN
  BEGIN
    insert_t1;
  EXCEPTION
    WHEN OTHERS THEN
      FOR rec IN csr LOOP
        dbms_output.put_line('COL: ' || rec.col);
      END LOOP;
      RAISE;
  END;
END;
/

COL: 1
COL: 2
DECLARE
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 15

This shows that if you exit a stored subprogram with an unhandled exception, Oracle does not do any rollback as the records inserted by the first insert statement are still in the table. However, as above when executing the DML directly, the whole of the second insert statement has been rolled back to the implicit savepoint established before the second statement was executed.

However, if we then attempt to query the table.

SELECT col
FROM t
/

no rows selected.

This shows that if you exit an anonymous block with an unhandled exception Oracle does do rollback. This will again be to the implicit savepoint established before the anonymous block was executed.

Upvotes: 5

Related Questions