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

1  –  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   
                                            

3  –  Arguments

3.1  –  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.

4  –  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