REF Cursor ISOPEN Behavior Different Between 11.2.0.1 and 11.2.0.2 (Doc ID 1324674.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 11.2.0.2 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

When using ISOPEN to check whether a REF CURSOR is still opened or not behaves differently
between the 11.2.0.1 and 11.2.0.2 database.

The following code was run in both a 11.2.0.1 database and a 11.2.0.2 database and produces different results.

In the 11.2.0.1.0 database. the second time the fetch is done the cursor remains opened.
In the 11.2.0.2.0 database. the second time the fetch is done the cursor is closed.

DECLARE
   in_query VARCHAR2(2000) := 'select ''Simple Test'' from dual';
   lv_prod_id_qual VARCHAR2(100);
   TYPE lv_type_ref_cur IS REF CURSOR;
   lv_ref_cur lv_type_ref_cur;
BEGIN
   OPEN lv_ref_cur FOR in_query;
   LOOP
      IF lv_ref_cur%ISOPEN THEN
         DBMS_OUTPUT.PUT_LINE('1st check: Cursor is open');
         FETCH lv_ref_cur INTO lv_prod_id_qual;
      END IF;

      IF lv_ref_cur%ISOPEN THEN
         DBMS_OUTPUT.PUT_LINE('2nd check: Cursor is open');
         EXIT WHEN lv_ref_cur%NOTFOUND;
      ELSE
         DBMS_OUTPUT.PUT_LINE('2nd check: Cursor is NOT open');
         EXIT;
      END IF;
   END LOOP;
END;
/


This is the result from the 11.2.0.1 database.

1st check: Cursor is open
2nd check: Cursor is open
1st check: Cursor is open
2nd check: Cursor is open

PL/SQL procedure successfully completed.

This is the result from the 11.2.0.2 database.

1st check: Cursor is open
2nd check: Cursor is open
1st check: Cursor is open
2nd check: Cursor is NOT open

PL/SQL procedure successfully completed.


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms