Re: Closing PL/SQL cursor
Date: Mon, 27 Sep 1999 15:30:22 -0400
Message-ID: <kcXvN+umPV4ZvfyXpVOL9yZN=8ie_at_4ax.com>
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 !
>
[Quoted] 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 CorporationReceived on Mon Sep 27 1999 - 21:30:22 CEST