Re: Closing PL/SQL cursor

A copy of this was sent to Yann Chevriaux <chevriaux_at_theleme.com> (if that email address didn't require changing) On Mon, 27 Sep 1999 11:05:28 +0200, you wrote:

>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 !
>

see the init.ora parameter close_cached_open_cursors. set it to true. on commit, it'll close these cached cursors (but performance will be affected on subsequent executes).

setting max cursors to 500 or so might be a better solution..

>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)
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Sep 27 1999 - 14:30:22 CDT