1  SET_HOLD_CURSORS
   Specifies the session default attributes for holdable cursors
   that have not been previously defined.
 

2  Environment
   You can use the SET HOLD CURSORS statement:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled to change
      the behavior of dynamic cursors

   o  As part of a procedure in an SQL module

   o  In dynamic SQL as a statement to be dynamically executed
 

2  Format

 (B)0SET HOLD CURSORS qwq> variable qqqqqqqwq> 
                   mq> string-literal qj   
                                           

 

2  Arguments
 

3  variable

   Syntax options:

   variable | string-literal

   Specifies the attribute for the holdable cursor. Values can
   include:

   o  ON COMMIT

      All cursors declared without a WITH HOLD clause or with a WITH
      HOLD ON COMMIT clause remain open when you commit.

   o  ON ROLLBACK

      All cursors declared without a WITH HOLD clause or with a WITH
      HOLD ON ROLLBACK clause remain open when you roll back.

   o  ALL

      All cursors remain open with the exception of those declared
      with a WITH HOLD clause.
 

   o  NONE

      All cursors close with the exception of those declared with a
      WITH HOLD clause.

      This is the default if you do not specify a SET HOLD CURSORS
      statement.
 

2  Example
   Example 1: Setting session default attributes for holdable
   cursors

   SQL> ATTACH 'FILENAME mf_personnel';
   SQL> --
   SQL> -- Define the session default
   SQL> --
   SQL> SET HOLD CURSORS 'ON ROLLBACK';
   SQL> --
   SQL> -- Declare the cursor
   SQL> --
   SQL> DECLARE curs1 CURSOR FOR
   cont> SELECT first_name, last_name FROM employees;
   SQL> OPEN curs1;
   SQL> FETCH curs1;
    FIRST_NAME   LAST_NAME
    Terry        Smith
   SQL> FETCH curs1;
    FIRST_NAME   LAST_NAME
    Rick         O'Sullivan
   SQL> DELETE FROM employees WHERE CURRENT OF curs1;
   1 row deleted
   SQL> ROLLBACK;
   SQL> FETCH curs1;
    FIRST_NAME   LAST_NAME
    Stan         Lasch
   SQL> COMMIT;
   SQL> FETCH curs1;
   %SQL-F-CURNOTOPE, Cursor CURS1 is not opened

   Example 2: Overriding the session default attributes for holdable
   cursors

   SQL> -- Set the session default
   SQL> --
   SQL> SET HOLD CURSORS 'ALL';
   SQL> --
   SQL> -- Declare the cursor without a WITH HOLD clause
   SQL> --
   SQL> DECLARE curs2 CURSOR FOR
   cont> SELECT first_name, last_name FROM employees;
   SQL> OPEN curs2;
   SQL> FETCH curs2;
    FIRST_NAME   LAST_NAME
    Terry        Smith
   SQL> FETCH curs2;
    FIRST_NAME   LAST_NAME
    Rick         O'Sullivan
   SQL> ROLLBACK;
   SQL> FETCH curs2;
    FIRST_NAME   LAST_NAME
    Stan         Lasch
   SQL> COMMIT;
   SQL> FETCH curs2;
    FIRST_NAME   LAST_NAME
    Susan        Gray
   SQL> CLOSE curs2;
   SQL> FETCH curs2;
   %SQL-F-CURNOTOPE, Cursor CURS2 is not opened
   SQL> --
   SQL> -- Declare the cursor overriding the session default by
   SQL> -- specifying the WITH HOLD clause
   SQL> --
   SQL> DECLARE curs3 CURSOR
   cont> WITH HOLD PRESERVE ON COMMIT
   cont> FOR SELECT first_name, last_name FROM employees;
   SQL> OPEN curs3;
   SQL> FETCH curs3;
    FIRST_NAME   LAST_NAME
    Terry        Smith
   SQL> FETCH curs3;
    FIRST_NAME   LAST_NAME
    Rick         O'Sullivan
   SQL> COMMIT;
   SQL> FETCH curs3;
    FIRST_NAME   LAST_NAME
    Stan         Lasch
   SQL> ROLLBACK;
   SQL> FETCH curs3;
   %SQL-F-CURNOTOPE, Cursor CURS3 is not opened