RE: PL SQL cursor help

From: "Justin Cave (DDBC)" <jcave@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 19 Mar 2004 19:54:20 -0700
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
-----------------------------------------------------------------
RE: PL SQL cursor help

Other related posts:
