REF CURSOR and ORA-01000

From: <pjanes_at_my-dejanews.com>
Date: Thu, 04 Feb 1999 00:14:17 GMT
Message-ID: <79aooj$9v8$1@nnrp1.dejanews.com>

I have a package defined as follows...

/*************************************************/
create or replace package test_acm_types_pkg as

        type acm_types_cur is ref cursor;

procedure test_acm_types(p_return_cursor IN OUT acm_types_cur);

end;
/

create or replace package body test_acm_types_pkg as

procedure test_acm_types(p_return_cursor IN OUT acm_types_cur) is

begin

        open p_return_cursor for
                select accom_type, accom_type_description
                from accom_type;

end test_acm_types;

end;
/*************************************************/
/

I call the procedure test_acm_types with the following:

/*************************************************/
var result refcursor

exec test_acm_types_pkg.test_acm_types(:result);

print result;
/*************************************************/

If I run the above code 50 times, I receive the oracle error ORA-01000 (maximum open cursors exceeded).

I know that I can increase this number and this is our workaround for now, but I want to know why these cursors are not closing in the first place.

We use the above procedure from ADO from an ASP page and the same error occurs. We are attempting to close the cursor with an rs.Close but it has NO effect.

When I look at the view V$OPEN_CURSOR, it has 50 *duplicate* entries as per the following:

SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_TEXT

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

30078968 7 ZEBPONYMAN 302FC9E8 -362312171 SELECT ACCOM_TYPE, NB: Each of the address, hash_value(s) are *identical*???

So, why are these cursors not closing? And if they are not, why does Oracle not re-use the cached cursor entries when I next call the stored procedure???

Pat Janes
Atlas Travel Technologies
e-mail: pjanes_at_atlasmail.com

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Feb 03 1999 - 18:14:17 CST