RE: OPEN_CURSORS !

  • From: "Justin Cave" <justin@xxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 14 Jul 2004 18:14:40 -0600

Going from memory a bit here...

I believe that when you're using CURSOR, there is one open cursor for every
row in the array that you are fetching.  In SQL*Plus, for example, you will
need 1 open cursor * the value of arraysize.  If you set arraysize to
something small, your error will likely disappear.  Whatever client language
you're using will have something similar.


Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of SRIDHARAN, SAN (SBCSI)
Sent: Wednesday, July 14, 2004 3:01 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: OPEN_CURSORS !

When I execute the following query, the number cursors opened increase with
the number of rows returned by the query and I get the oracle error "maximum
cursor opened".=20

Is there a way I can reuse the already opened cursor and not hit the maximum
OPEN_CURSOR value?


SELECT po.prod_offer_id, pot.dscr, po.qual_req, po.prod_id, pt.dscr ,
       CURSOR (SELECT pi.prod_iden_id, pi.parent_id, pit.dscr, pi.VALUE
                 FROM PROD_IDEN pi, PROD_OFFER_IDEN poi, PROD_IDEN_TYPE pit
                WHERE pit.prod_iden_type_id =3D pi.prod_iden_type_id
                  AND pi.prod_iden_id =3D poi.prod_iden_id
                  AND poi.prod_offer_id =3D po.prod_offer_id
              )
                            FROM PROD_OFFER po,
       PROD_OFFER_TYPE pot,
       PROD p,
       PROD_TYPE pt,
       PROD_OFFER_AUDNC_PRFLE poap
 WHERE po.prod_offer_type_id =3D pot.prod_offer_type_id
   AND po.prod_id =3D p.prod_id
   AND pt.prod_type_id =3D p.prod_type_id
   AND po.prod_offer_id =3D poap.prod_offer_id
   AND poap.audnc_prfl_id =3D 16
   AND pt.dscr IN ('LD');

Thanks,
San
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------




----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: