How do you close a ref cursor out of the block that opens it ?

From: Christophe Dary <cdary_at_usa.net>
Date: Mon, 27 Sep 1999 09:21:39 +0200
Message-ID: <7sn5s7$3ia$1@wanadoo.fr>

I use Oracle 8.0.5 for Linux and Client 8.0.4 for Windows NT.

Each time I open a REF CURSOR and don't close it immediately in the same block but later in another block, it is added to the opened cursors view.

Of course, when I reach the OPEN_CURSORS value I got an error.

I have read that REF CURSORS are never cached so it cannot be the reason.

Here is the code in SQL*Plus :

SQL> VAR CV REFCURSOR
SQL> BEGIN
2 OPEN :CV FOR SELECT * FROM GPC.AGENCE 3 END;
4 /

Procédure PL/SQL terminée avec succès.

SQL> PRINT CV

AGENCE_ID AGENCE_NOM                     A
--------- ------------------------------ -
        1 Paimpol                        O
        2 St Pol de Léon                 O
        3 Rennes                         O
        4 Nantes                         O
        5 Laval                          O
        6 Lyon                           O
        7 Grenoble                       O
        8 Brest                          O
        9 Challans                       O
       10 Angers                         O
       11 Toulouse                       O

11 ligne(s) sélectionnée(s).

normally PRINT CV close the cursor (and actually it does) but for more security I add what follows :

SQL> BEGIN
  2 IF :CV%ISOPEN THEN
  3 CLOSE :CV;
  4 END IF;
  5 END;
  6 /

Procédure PL/SQL terminée avec succès.

SQL> SELECT SQL_TEXT FROM V$OPEN_CURSOR; SQL_TEXT

SELECT * FROM GPC.AGENCE
SELECT SQL_TEXT FROM V$OPEN_CURSOR SQL>

The cursor still shows in the V$OPEN_CURSOR view
if I do this again I get another line, and so on ...

I have found nothing on this on the web except someone asking for the same question last February.
Is it a bug in Oracle 8.0.5 ? Received on Mon Sep 27 1999 - 02:21:39 CDT