| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PL SQL cursor help
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_at_ddbcinc.com 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stephen Andert
Sent: Friday, March 19, 2004 7:16 PM
To: oracle-l_at_freelists.org
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;
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;
/
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Fri Mar 19 2004 - 21:11:41 CST
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
 |
 |