Anurag,

The way you are doing this is totaly wrong. I can't say why you are getting this "Fetch out of sequence" error, but even if you didn't get this one you will most certanly get another nasty one: ORA-1555: Snapshot to old...

What you are doing is called "fetch accros commit" - you open a cursor, feTch some record from it, perform some changes to the table you are fetching from, commit the changes and continue fetching from this same cursor without closing it and opening it again. The perfect scenario for ORA-1555. The more often you commit, the sooner you'll get ORA-1555.

The most simple way for your example would be to commit every N records (say N=100,000), but to open a new cursor (implicit or explicit one) after each commit:

LOOP
DELETE FROM test WHERE rownum <= 100000 AND ....;
EXIT WHEN SQL%ROWCOUNT = 0;
END;