IBM Support

IC87349: DYNAMIC PL/SQL CURSORS ARE CLOSED AFTER A COMMIT

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • If your PL/SQL application opens a cursor using dynamic SQL
    text, the cursor will be closed after a COMMIT operation.  This
    is incorrect for PL/SQL code, where cursors should remain open
    after a commit.  In other words, they should exhibit the same
    behaviour as an equivalent SQL PL cursor declared with the WITH
    HOLD attribute.
    
    The following test script demonstrates the behaviour.  The
    CLOSE statement should complete successfully; instead, it fails
    with error SQL0501N:
    
    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.
    
    
    /* db2set DB2_COMPATIBILITY_VECTOR=ORA */
    
    set sqlcompat plsql;
    set serveroutput on;
    
    declare
      dummy number(10);
    
      type r_cursor is REF CURSOR;
      dyn_cursor r_cursor;
    
      sql_text varchar(2000);
    
    begin
      -- Open a dynamic cursor
      sql_text := 'SELECT 1 FROM DUAL' ;
    
      open dyn_cursor for sql_text;
      dbms_output.put_line('Dynamic cursor opened.');
    
      loop
        fetch dyn_cursor into dummy;
        exit when dyn_cursor%NOTFOUND;
    
        commit;
    
        dbms_output.put_line('Value fetched from dynamic cursor.');
      end loop;
    
      -- The cursor should still be open after the COMMIT.
      close dyn_cursor;
      dbms_output.put_line('Dynamic cursor closed.');
    end;
    /
    
    connect reset;
    terminate;
    

Local fix

  • If a dynamic cursor is required and it is necessary to keep the
    cursor open through a commit operation, consider writing a
    helper routine in SQL PL (instead of PL/SQL) to dynamically
    open a cursor variable WITH HOLD.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.7 Fix Pack 8.                       *
    ****************************************************************
    

Problem conclusion

  • This problem is fixed in DB2 Version 9.7 Fix Pack 8.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC87349

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-10-18

  • Closed date

    2013-05-01

  • Last modified date

    2013-05-01

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC87406

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

  • RA10 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC87349

Modified date: 01 May 2013