Contents Index Controlling the isolation level Introduction to Java in the Database

ASA Programming Guide
  Using SQL in Applications
    Controlling transactions in applications

Cursors and transactions


In general, a cursor closes when a COMMIT is performed. There are two exceptions to this behavior:

If either of these two cases is true, the cursor remains open on a COMMIT.

ROLLBACK and cursors 

If a transaction rolls back, then cursors close except for those cursors opened WITH HOLD. However, don't rely on the contents of any cursor after a rollback.

The draft ISO SQL3 standard states that on a rollback, all cursors (even those cursors opened WITH HOLD) should close. You can obtain this behavior by setting the ANSI_CLOSE_CURSORS_AT_ROLLBACK option to ON.

Savepoints 

If a transaction rolls back to a savepoint, and if the ANSI_CLOSE_CURSORS_AT_ROLLBACK option is ON, then all cursors (even those cursors opened WITH HOLD) opened after the SAVEPOINT close.

Cursors and isolation levels 

You can change the isolation level of a connection during a transaction using the SET OPTION statement to alter the ISOLATION_LEVEL option. However, this change affects only closed cursors.


Contents Index Controlling the isolation level Introduction to Java in the Database