Re: Closing Explicit Cursors

QWERTY wrote:
> Hi,
> We are using explicit cursors in select statements as shown
> below:
>
> SELECT A.OID OID,PUR_ORD_NUMBER,PUR_ORD_DATE,PUR_VENDOR_CD,
> (SELECT NAME FROM BV_USER X,BV_USER_PROFILE Y WHERE X.USER_ID =
> Y.USER_ID AND USER_ALIAS = PUR_VENDOR_CD)PUR_VENDOR_NAME,
> PUR_PLANT_CD,PUR_PAY_TERM,PUR_DLVRY_TERM,PUR_CUR_CODE,PUR_PKG_FWD_DET,
> CURSOR(SELECT PDL_LINE_NO,PDL_PART_NO,(SELECT VMT_PART_DESC FROM
> bvoracle.TX_BV_VENDOR_MTL WHERE VMT_PART_NO = PDL_PART_NO AND
> VMT_VENDOR_CD = PUR_VENDOR_CD AND ROWNUM < 2) PDL_PART_DESC,
> PDL_UNIT_PRICE,PDL_ORD_QTY,PDL_PUR_UOM,PDL_TAX_AMT,PDL_EXCISE_DUTY,PDL_LINE_AMT,PDL_DLVY_DATE
> FROM
> bvoracle.TX_BV_PRCH_ORD_LINES B WHERE A.OID = B.OID) CHILD
> FROM
> bvoracle.TX_BV_PRCH_ORDERS A
> WHERE
> DELETED = 0 AND STATUS = 1 AND STORE_ID = 102 AND
> PUR_ORD_NUMBER not IN (SELECT SAM_PO_NUMBER FROM TX_BV_SAMPLE_FBK ) AND
> A.OID IN (SELECT OID FROM bvoracle.TX_BV_PRCH_ORD_LINES WHERE DELETED =
> 0 AND STATUS = 1 AND STORE_ID = 102 AND PDL_DLVY_DATE > SYSDATE)
>
> After using such queries,we are getting the following errors:
>
> ORA-01000: maximum open cursors exceeded
> SQL_MSG: ORA-00604: error occurred at recursive SQL level 1
>
> The current value of Open_cursors is 300.
>
> let us know how the explicit cursors can be closed.

I don't see an explicit cursor anywhere in what you posted.

You need to look elsewhere for what is and is not being closed.

And then following that investigation ... likely increase the value of open_cursors.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)