Sqlcode=-501, Sqlstate=24501 with Cursor

Sqlcode=-501, Sqlstate=24501 with Cursor

I have written one procedure. I am getting the below error. Can someone help me with this.

Create procedure p1()

declare cur1 cursor WITH HOLD for STMT1;

DECLARE CONTINUE HANDLER FOR SQLSTATE '24501' 
BEGIN
OPEN index_list ;
set exitcode = 1;

END ;  

set TEXT =();
PREPARE STMT1 FROM TEXT;     

OPEN cur1 ;

 FETCH FROM cur1 INTO VAR2,
                            VAR1 ;

 WHILE (SQLCODE = 0)
       DO
        EXECUTE IMMEDIATE 'SET PASSTHRU SAMPLE' ;
        ----
        -----
        EXECUTE IMMEDIATE 'SET PASSTHRU RESET' ;       

FETCH FROM cur1 INTO VAR2,
                            VAR1 ; 

END WHILE;

This is my code outline. My problem is After that 'SET PASSTHRU RESET', cursor is getting closed & the second fetch is failing with the below error.even I specified the curos a WITH HOLD option.

error: The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open.. SQLCODE=-501, SQLSTATE=24501, DRIVER=4.17.30 Run of routine failed.

can some one please let me know, what i have to add here. I am new bee for db2 & never met with this scenario. That is why I am asking the experts.

3
Marcus Vance
Author

Marcus Vance

Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.