Either that or autonomous transaction in another function. On 03/19/2004 10:14:14 PM, Stephen Andert wrote: > Justin, > > See, I knew it was a basic problem. > > This is a one-time fix (hopefully :) and it is running successfully > without the FOR UPDATE. That's what you get when youcut/paste from too > many different examples. > > Chalk another success up to oracle-l and the great members thereof. > > Thanks! > Stephen > > >>> jcave@xxxxxxxxxxx 03/19/04 07:54PM >>> > Fundamentally, you have a problem. When you do a SELECT ... FOR > UPDATE, you lock all the rows you are SELECT-ing. When you commit, > however, you have to release all your locks. This invalidates the > cursor, so you can no longer fetch rows from it. > > The best way to do this sort of update would be to do it as a single > SQL statement. If this is a one-time operation, and you don't need to > lock the rows to prevent other users from updating them, you may be able > to get away with just removing the FOR UPDATE clause, but this isn't > likely to be an ideal solution long-term. > > > Justin Cave > Distributed Database Consulting, Inc. > http://www.ddbcinc.com/askDDBC > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Stephen Andert > Sent: Friday, March 19, 2004 7:16 PM > To: oracle-l@xxxxxxxxxxxxx > Subject: PL SQL cursor help > > I've been RTFM's and Googling and I've gotten this far, but now I'm > tired and want to go home so I hope someone has fresh eyes to assist. > > I'm testing for a table update that will update 27 million rows of a > 150 million row table (sma) with a value from a second table (mr15421). > > My goal is to run one command and update 27 million rows 10,000 or so > at a time. > > This code works, but when I take out the comments in front of the > commit, I get an error: > declare > * > ERROR at line 1: > ORA-01002: fetch out of sequence > ORA-06512: at line 12 > > It is probably something basic that I'm overlooking. I hope one of you > will see it and point it out to me. > > Thanks > Stephen > > declare > i number := 0; > cursor s1 is SELECT * FROM sma a WHERE > exists (select 1 from mr15421 b > where a.sched_id = b.sched_id > and a.proc_cd = b.proc_cd > and a.proc_catg_cd = b.proc_catg_cd > and a.compnnt_typ_cd = b.compnnt_typ_cd > and a.eff_dt = b.eff_dt) > FOR UPDATE; > begin > for c1 in s1 loop > dbms_output.put_line (i); > i := i +1; > if i > 2 then > dbms_output.put_line (i); > -- commit; > i := 0; > end if; > end loop; > -- commit; > end; > / > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put > 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > -- Mladen Gogala Oracle DBA ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------