SQL0502N The cursor specified in an OPEN statement is already open. SQLSTATE=24502

Sri Prardhan

SQL0502N The cursor specified in an OPEN statement is already open. SQLSTATE=24502

Hi All 

I have written the following db2 procedure.

 

 

FOR c2_ord AS c2 CURSOR FOR
--DECLARE c2 CURSOR FOR
select distinct ppi.order_id from wcs7_orders ord,wcs7_ppcpayinst ppi where ppi.order_id = ord.orders_id
AND ord.status not in ('P','X')
AND ppi.paymentsystemname = 'SimplePunchout'
AND ppi.state = 1
AND ord.timeplaced > DATE(DAYS(CURRENT_DATE)-1095) with ur

DO

--FOR c2_ord IN c2 LOOP
-- DBMS_OUTPUT.PUT_LINE(c2_ord.order_id);
SET V_COUNTER = V_COUNTER+1;
CALL DBMS_OUTPUT.PUT_LINE('V_COUNTER');

--EXEC SQL DECLARE c1 CURSOR FOR
--SELECT * FROM WCS7_PPCPAYINST where order_id = c2_ord.order_id with ur;

FOR c1_ord AS c1 CURSOR FOR
SELECT * FROM WCS7_PPCPAYINST where order_id=c2_ord.order_id with ur

Do

OPEN c1;
FETCH c1
INTO V_PPCPAYINST_ID,V_ORDE........

It is throwing the below error message while calling 

SQL0502N  The cursor specified in an OPEN statement is already open. SQLSTATE=24502

Can any one help me to resolve this error. I have used 2 for loops with 2 cursor variables. 

I need 2 loops for my requirement. 

is using one for cursor with in another for for cursor is wrong ?

Pls guide me

 

 

 

 

James Campbell

SQL0502N The cursor specified in an OPEN statement is already open. SQLSTATE=24502
(in response to Sri Prardhan)
Do you close the cursors?

James Campbell


On 1 Nov 2017 at 6:37, Sri Prardhan wrote:

>
> Hi All 
> I have written the following db2 procedure.
>  
>  
> FOR c2_ord AS c2 CURSOR FOR
> --DECLARE c2 CURSOR FOR
> select distinct ppi.order_id from wcs7_orders ord,wcs7_ppcpayinst ppi where ppi.order_id =
> ord.orders_id
> AND ord.status not in ('P','X')
> AND ppi.paymentsystemname = 'SimplePunchout'
> AND ppi.state = 1
> AND ord.timeplaced > DATE(DAYS(CURRENT_DATE)-1095) with ur
>
> DO
>
> --FOR c2_ord IN c2 LOOP
> -- DBMS_OUTPUT.PUT_LINE(c2_ord.order_id);
> SET V_COUNTER = V_COUNTER+1;
> CALL DBMS_OUTPUT.PUT_LINE('V_COUNTER');
>
> --EXEC SQL DECLARE c1 CURSOR FOR
> --SELECT * FROM WCS7_PPCPAYINST where order_id = c2_ord.order_id with ur;
>
> FOR c1_ord AS c1 CURSOR FOR
> SELECT * FROM WCS7_PPCPAYINST where order_id=c2_ord.order_id with ur
>
> Do
>
> OPEN c1;
> FETCH c1
> INTO V_PPCPAYINST_ID,V_ORDE........
> It is throwing the below error message while calling 
> SQL0502N  The cursor specified in an OPEN statement is already open. SQLSTATE=24502
> Can any one help me to resolve this error. I have used 2 for loops with 2 cursor variables. 
> I need 2 loops for my requirement. 
> is using one for cursor with in another for for cursor is wrong ?
> Pls guide me
>  
>  
>  
>  
>
>
> Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list  
> Manage your subscription  
>
> This email has been sent to: [login to unmask email]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>