Re: PL SQL cursor help

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

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
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
-----------------------------------------------------------------