WITH HOLD Cursors - IBM DB2 to Oracle Migration

In DB2 you can use WITH HOLD clause in DECLARE CURSOR statement to specify that the cursor remains open when a COMMIT is issued. By default, DB2 closes a cursor on COMMIT.

Oracle does not provide WITH HOLD and WITHOUT HOLD clauses in cursor declaration, but a cursor remains open after COMMIT unless FOR UPDATE is specified.

Let's see how WITH HOLD works in DB2. First we will create a sample table with data:

DB2:

Note: Run DB2 CLP with options -t +c to set ; as the statement delimiter and turn autocommit off.

  -- Sample table
  CREATE TABLE colors (name VARCHAR(30));
 
  INSERT INTO colors VALUES ('Green');
  INSERT INTO colors VALUES ('Black');
  INSERT INTO colors VALUES ('White');
  COMMIT;

By default DB2 opens a cursor WITHOUT HOLD that means the cursor is automatically closed when a COMMIT is executed:

DB2:

  --#SET TERMINATOR @
 
  BEGIN
    DECLARE v_name VARCHAR(30);
 
    -- Cursor is WITHOUT HOLD by default
    DECLARE cur CURSOR FOR SELECT name FROM colors;
 
    OPEN cur;      
    -- COMMIT will close the cursor 
    COMMIT;
 
    -- Cursor already closed
    FETCH cur INTO v_name;
 
    CLOSE cur;
  END @
 
  # SQL0501N  The cursor specified in a FETCH statement or CLOSE statement is not
  # open or a cursor variable in a cursor scalar function reference is not open.
  # SQLSTATE=24501
 
  --#SET TERMINATOR ;

Now let's declare the cursor WITH HOLD option:

DB2:

  --#SET TERMINATOR @
 
  BEGIN
    DECLARE v_name VARCHAR(30);
 
    DECLARE cur CURSOR WITH HOLD FOR SELECT name FROM colors;
 
    OPEN cur;      
    -- COMMIT will not close the cursor now 
    COMMIT;
 
    -- Cursor is still open
    FETCH cur INTO v_name;
 
    CLOSE cur;
  END @
 
  # DB20000I  The SQL command completed successfully.
 
  --#SET TERMINATOR ;

Oracle does not provide WITH HOLD and WITHOUT HOLD clauses, but a cursor remains open after COMMIT is executed unless FOR UPDATE is specified.

Oracle:

  DECLARE 
    v_name VARCHAR2(30);
 
    CURSOR cur IS SELECT name FROM colors;
  BEGIN
 
    OPEN cur;     
    -- COMMIT does not close the cursor 
    COMMIT;
 
    -- A row can be successfully fetched after COMMIT
    FETCH cur INTO v_name;
 
    CLOSE cur;
  END;
  /
 
  # PL/SQL procedure successfully completed.

But if a cursor is declared with FOR UPDATE clause, it is closed on COMMIT:

Oracle:

  DECLARE 
    v_name VARCHAR2(30);
 
    -- Declare FOR UPDATE cursor 
    CURSOR cur IS SELECT name FROM colors FOR UPDATE;
  BEGIN
 
    OPEN cur;     
    -- COMMIT closes the cursor now 
    COMMIT;
 
    -- Fetch statement returns ORA-01002 error
    FETCH cur INTO v_name;
 
    CLOSE cur;
  END;
  /
 
  # ERROR at line 1:
  # ORA-01002: fetch out of sequence
  # ORA-06512: at line 13

• IBM DB2 to Oracle Migration Tools and Reference
• Online SQL Conversion Tool

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - September 2013.