Closing PL/SQL cursor

From: Yann Chevriaux <chevriaux_at_theleme.com>
Date: Mon, 27 Sep 1999 11:05:28 +0200
Message-ID: <37EF3358.C8EA937C@theleme.com>

I've got a problem:

   It seems that cursors never closed into a session !

CREATE OR REPLACE FUNCTION get_dummy
RETURN VARCHAR2 IS
 foo varchar2(1);
 cursor c_dummy is
   select dummy from dual;
BEGIN
  open c_dummy;
  fetch c_dummy into foo;
  close c_dummy;
  return foo;
EXCEPTION
   WHEN OTHERS THEN

      if c_dummy%isopen then
        close c_dummy;
     end if;
     raise;

END; SQL> select get_dummy from dual;

GET_DUMMY

---

X

SQL> select sid, user_name, sql_text from v$open_cursor;

      SID USER_NAME

--------- ------------------------------

SQL_TEXT

---

        7 SCOTT
select sid, user_name, sql_text from v$open_cursor

        7 SCOTT
SELECT DUMMY FROM DUAL It looks like a closed cursor still opened !

As I use many functions, database fails out of cursors if users don't disconnect and reconnect several times in a day (that's the only way I found to flush unused cursors !)

PLEASE HELP !!!!!! I need someone explain me how it works ...

Configuration:

   ServeurNT, Oracle 7.3.2
   OPEN_CURSORS = 110 (Never so much useful at a time) Received on Mon Sep 27 1999 - 04:05:28 CDT