Re: Using UPDATE with ROWNUM?

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 30 Jun 2007 07:59:03 -0700
Message-ID: <1183215467.136012@bubbleator.drizzle.com>

Hasta wrote:
> In article <1183151689.328262_at_bubbleator.drizzle.com>,
> damorgan_at_psoug.org says...

>> Hasta wrote:
>>
>>> But why would the OP get ora-1555, Joel (and Frank and Daniel)  ?
>>>
>>> He is not commiting in a cursor loop, afaics
>>>
>>> begin
>>>   loop
>>>     update (select seq_no from mytable) set seq_no = tempseq.nextval
>>>       where rownum <= 1000;
>>>     exit when SQL%FOUND = false;
>>>     commit;
>>>   end loop;
>>> end;
>>>
>>> --- Raoul
>> Lets see:
>>
>>    EXIT ...
>>    COMMIT ...
>> END LOOP ...
>>
>> He isn't?
>>

>
> I meant that he is not fetching accross commit,
> Daniel - that is, the loop is not of the kind :
>
> for i in (select ... )
> loop
> ...
> commit;
> end loop;
>
> I'll have to ckeck "One on One", because - call me
> stupid - I still dont get it. The implicit cursor
> underlying the update statement is closed after
> execution of the statement, within the loop body.
> I dont see why a pre-image should have to be rebuilt
> from rollback segments across loop body executions.
>
> --- Raoul

Literally you are correct and I will admit that I didn't look at the statement closely enough to see that you said "cursor loop" rather than loop.

But now, having looked at the statement very closely, I have another comment to make ... what is it that prevents the loop from updating the exact same 1000 rows ad infinitum?

CREATE TABLE test AS
SELECT object_id, created
FROM dba_objects;

CREATE SEQUENCE tempseq;

SELECT COUNT(*) FROM test;

SELECT COUNT(*) FROM test WHERE created > SYSDATE;

• turned the LOOP into a FOR loop to avoid killing the session BEGIN FOR i IN 1 .. 10 LOOP UPDATE (select object_id, created FROM test) set object_id = tempseq.nextval, created = SYSDATE+1 WHERE rownum <= 1000; EXIT WHEN SQL%FOUND = FALSE; COMMIT; END LOOP; END; /

SQL> SELECT COUNT(*) FROM test

   2 WHERE created > SYSDATE;

   COUNT(*)

---

       1000

SQL> SELECT last_number

   2 FROM user_sequences
   3 WHERE sequence_name = 'TEMPSEQ';

LAST_NUMBER

---

       10001

SQL> That is 1000 rows updated 10 times ... not 10,000 rows updated.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Jun 30 2007 - 09:59:03 CDT