PL/SQL (MOSC)

MOSC Banner

Cursor with commits? - Updates 33 millions rows fills the UNDO

edited Jul 21, 2010 1:57AM in PL/SQL (MOSC) 11 commentsAnswered
The simple query below updates 33 millions rows, but terminates after filling my undo.  What is the best way to batch this update with commits and a cursor.   I appreciate your help. 

update productview set CUSTOMER_ID = 'value2' where CUSTOMER_ID = 'none';

I just tried the code below but received error ORA-01002 fetch out of sequence:

declare
i number := 0;
cursor s1 is select * from test_productview where customer_id = 'none' FOR UPDATE;
begin
for c1 in s1 loop
update test_productview set CUSTOMER_ID = 'value2'
where current of s1;

i := i + 1; -- Commit after every X records
if i > 1000 then

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center