Closing Explicit Cursors

From: QWERTY <krishshankar_at_gmail.com>
Date: 5 Dec 2005 21:37:56 -0800
Message-ID: <1133847476.866831.243200@g49g2000cwa.googlegroups.com>

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. Received on Mon Dec 05 2005 - 23:37:56 CST