Re: 01555 and select statement

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 10 May 2006 16:20:39 -0700
Message-ID: <1147303240.916144@bubbleator.drizzle.com>

niy38_at_hotmail.com wrote:
> how can that happen?
>
>
> for rec in (select * from a_big_table where a=1)
> loop
> update a_big_table set a=0;
> commit;
> end loop
>

This is legacy code. Replace it with:

TYPE myarray IS TABLE OF a_big_table%ROWTYPE; l_data myarray;

CURSOR r IS
SELECT *
FROM a_big_table
WHERE a=1;

BEGIN
   OPEN r;
   LOOP
     FETCH r BULK COLLECT INTO l_data LIMIT 500;

     FORALL i IN 1..l_data.COUNT
     UPDATE a_big_table SET a=0;  -- just copying what you did.

     EXIT WHEN r%NOTFOUND;

   END LOOP;
   COMMIT;
   CLOSE r;
END fast_way;
/

If the speed does not increase by at least 10X and the exceptions stop I'll be more than surprised.

-- 
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 Wed May 10 2006 - 18:20:39 CDT