RE: OPEN_CURSORS !

From: Justin Cave <justin_at_askddbc.com>
Date: Wed, 14 Jul 2004 18:14:40 -0600
Message-Id: <20040714235226.CA19B72C0FD@turing.freelists.org>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of SRIDHARAN, SAN (SBCSI)
Sent: Wednesday, July 14, 2004 3:01 PM
To: oracle-l_at_freelists.org
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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Wed Jul 14 2004 - 19:15:07 CDT