RE: PL SQL cursor help
- From: "Stephen Andert" <StephenAndert@xxxxxxxxxxxxxxx>
- To: jcave@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 19 Mar 2004 20:14:14 -0700
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
-----------------------------------------------------------------
Other related posts:
