Updating datefield in tables?

From: <yliu_at_creighton.edu>
Date: Fri, 30 Apr 1999 11:50:49 -0500
Message-ID: <Pine.HPP.3.95.990430113941.2925A-100000@penguin.creighton.edu>

Hi All,

Sorry I have to repost this topic again. When I run the following PL/SQL code,

Begin

        FOR c1 in date_cursor LOOP
                update my_table
                set thrudate = to_date('31-dec-2099', 'dd-mon-yyyy')
                where current of date_cursor;
        num_updated=num_updated+1;

        -- Inserted code below between IF... and END IF... lines
        IF num_updated = 5000 THEN
           commit;
           num_updated = 0;
        END IF;

        END LOOP

end;

I got the error message saying
"fetch out of sequence"
// *Cause: This may be caused by fetching from a 'select for update'cursor

//          after a commit.  A PL/SQL cursor loop implicitly does fetches
//          and may also cause this error.

// *Action:

At first, I thought the problem is the FOR LOOP. So I changed it to a WHILE LOOP. However, it still give me the same error message: 'fetch out of sequence'. Anybody know how to solve this problem?

P.S. I really want to break up my updating to smaller sub processes and I really like the idea to have a counter variable and check it's value. However, if I issue a commit, it seems that I can not get back to the FOR LOOP or WHILE LOOP to continue my updating. Any help would be appreciated.

Best Regards,

Yongge Liu
yliu_at_creighton.edu Received on Fri Apr 30 1999 - 11:50:49 CDT