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@(protected) [mailto:oracle-l-bounce@(protected)]
On Behalf Of SRIDHARAN, SAN (SBCSI)
Sent: Wednesday, July 14, 2004 3:01 PM
To: oracle-l@(protected)
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@(protected) 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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
