ROLLBACK

while i issue a FETCH after ROLLBACK, the cursor remain the record.
For example i have 3 records
1000, 2000, 3000 (transaction number)
when the 1000 has error it will rollback.
After that i issue FETCH in order to get 2000, but i found out it will fetch the 1000 again.
I like to know how to fetch the next record.

Try to commit after fetch since Rollback will bring you back to last commit point. I didn't try, but it's normal practice for commitment control.

Or you may prepare you select statement again to avoid bad record,open cursor and then fetch.

Hi,

When using COMMIT or ROLLBACK, the open cursors are closed.

You have to declare your cursor "WITH HOLD".
The WITH HOLD option on DECLARE CURSOR may be used to keep
the cursor open after a COMMIT or ROLLBACK.

Use ROLLBACK HOLD to keep the cursor open.

Birgitta