Cursor loooping error in SQL procedure

[Former Member]

I am getting an error,when looping through a cursor.Please find the code below.

c1 for SELECT Account,"Account Description" FROM "CHEP_DEMO"."TBL_PALLET_MOVEMENT";

/********* Begin Procedure Script ************/

BEGIN

open c1;

for cur_row as c1 do

end for;

close c1;

END;

I am getting an error,whenever i am having the for loop.if i remove the for loop i donot get an error.

any help is appreciated.

regards

Kaushik

[former_member184768]

Hi Kaushik,

You don't need to OPEN and CLOSE the cursor if you are using FOR loop.

As per the documentation for cursor FOR Loop:

Opens a previously declared cursor and iterates over each row in the result set of the query bound to the cursor. For each row in the result set the statements in the body of the procedure are executed. After the last row from the cursor has been processed, the loop is exited and the cursor is closed.

Please remove the OPEN and CLOSE statement and try the procedure. It will also help if you can post your complete procedure code to check if there is an error elsewhere too.

Regards,

Ravi

[Former Member]

Hi Kaushik,

I think that you may not use for ... loop out of begin .... end, you can use fetch c1 into xxx.

If you want to use for .. loop, you can move declare cursor to begin ... end.

Hope this can help you.

Regards,

Jerry

[Former Member]

Hello Jerry,

The problem doesnot seem with the cursor ,but if i write any loop statement like for loop i get an syntax error.

regards

kaushik

[AtulKumarJain]

Hi Kaushik,

What error you are getting ,please provide that.

screen shot ,log etc...

Best Regards,

Atul

[Former Member]

Hello Atul,

Actually i tried out again the error is not in cursor,but in the loop statement,any loop statement i write in the procedure i get an error saying "syntax error at end statement"

regards

Kaushik