Re: REF CURSOR and ORA-01000

We ran onto the same problem using Power Builder 5.3. to Oracle 7.3 We could exercise the reference cursor all day long from an PL_SQL block that had a close for the reference cursor without problems. As soon as we did that from a Power Builder window we would receive ORA-01000 (maximum open cursors exceeded). errors. After a lot of digging it looks like a reference cursor actually opens 2 cursors and Power Builder only closed 1 of them. It sound like you are having the same problem.

  We were never able to get the problem fixed. I here that Power Builder 6.5 does not have this problem. What we did was replace the reference cursor with a parameter driven view where possible or built a view from functions based on PL_SQL tables. I know corny, but it worked

Ted Clement
ted-clement_at_worldnet.att.net

pjanes_at_my-dejanews.com wrote in message <79aooj$9v8$1_at_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 Fri Feb 05 1999 - 23:19:38 CST