- When you use COMMIT or ROLLBACK statements with CURSOR, the behavior of the CURSOR depends on whether or not it is declared using the WITH HOLD clause
- If the CURSOR is declared using the WITHOUT HOLD clause, all of its resources (cursor, locks, and large-object datatype, or LOB, locators) are released upon either COMMIT or ROLLBACK.Therefore, if you need to use the cursor after completing a transaction, you will have to re-open the cursor and traverse it again from the first row.
- Defining a cursor using WITH HOLD will cause the cursor to maintain its position and some locks across transactions.
For cursors defined WITH HOLD after COMMIT:
- The cursor will remain open.
- The cursor will be positioned before the next logical row.
- The only permitted operations on cursors immediately after the COMMIT statement are FETCH and CLOSE.
- Positioned delete and positioned update are valid only for rows that are fetched within the same unit of work.
- All LOB locators will be released.
- All locks are released, except locks protecting the current cursor position of open WITH HOLD cursors. The locks held include the locks on the table and, for parallel environments, the locks on the rows where the cursors are currently positioned. Locks on packages and dynamic SQL sections (if any) are held.
- The set of rows modified by
- A data change statement
- Routines that modify SQL data embedded within open WITH HOLD cursors are committed
For cursors defined WITH HOLD after ROLLBACK
- All open cursors will be closed.
- All locks acquired during the UOW will be released.
- All LOB locators are freed.