Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: PL SQL cursor help

RE: PL SQL cursor help

From: Stephen Andert <StephenAndert_at_firsthealth.com>
Date: Fri, 19 Mar 2004 20:14:14 -0700
Message-ID: <s05b54bd.032@SLCM02.firsthealth.com>


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;

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_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
-----------------------------------------------------------------

----------------------------------------------------------------
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
-----------------------------------------------------------------
Received on Fri Mar 19 2004 - 21:11:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US