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.