Re: DBMS_ERRLOG will not compile within a cursor loop

"DA Morgan" <damorgan_at_psoug.org> schreef in bericht news:1193950181.169048_at_bubbleator.drizzle.com...
> Ken Denny wrote:
>> On Nov 1, 11:26 am, DA Morgan <damor..._at_psoug.org> wrote:
>>> Now my examples does not include a cursor loop. But if you have
>>> DMBS_ERRLOG then you are in a version of Oracle more recent than 8i.
>>> And that means you've no reason for ever writing a cursor loop: Ever!
>>
>> It can save you a lot of typing. Is it worth an extra 1000 keystrokes
>> in code to save one or two microseconds in execution time?
>
> Lets see ... with cursor loop.
>
> CREATE OR REPLACE PROCEDURE slow_way IS
>
> BEGIN
> FOR r IN (SELECT * FROM parent) LOOP
> -- modify record values
> r.part_num := r.part_num * 10;
> -- store results
> INSERT INTO child
> VALUES
> (r.part_num, r.part_name);
> END LOOP;
> COMMIT;
> END slow_way;
> /
>
> -- 7.71 seconds
>
> without cursor loop.
>
> CREATE OR REPLACE PROCEDURE fast_way IS
>
> TYPE myarray IS TABLE OF parent%ROWTYPE;
> l_data myarray;
>
> CURSOR r IS
> SELECT part_num, part_name
> FROM parent;
>
> BEGIN
> OPEN r;
> LOOP
> FETCH r BULK COLLECT INTO l_data LIMIT 1000;
>
> FOR j IN 1 .. l_data.COUNT
> LOOP
> l_data(1).part_num := l_data(1).part_num * 10;
> END LOOP;
>
> FORALL i IN 1..l_data.COUNT
> INSERT INTO child VALUES l_data(i);
>
> EXIT WHEN r%NOTFOUND;
> END LOOP;
> COMMIT;
> CLOSE r;
> END fast_way;
> /
>
> -- 0.50 seconds
>
> <SARCASM>
> Oh my gawd my fingers are killing me. All the heavy lifting and hard
> work. Please excuse me for a minute. I need to sit down and catch my
> breath ... make an appointment to have my carpel tunnel syndrome
> treated ... make out my will.
>
> And all I did was speed the code up by a lousy 15.42 times. Hardly
> worth it eh.
> </SARCASM>
>
> Good thing the web is near anonymous Ken. You might have just talked
> yourself out of quite a few job opportunities if recruiters look for
> you on the web. A few microseconds my ....
> --
> 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

Daniel,

I think error handling should be at the start of the loop and could be

EXIT WHEN l_data.COUNT = 0;
which might save another millisecond... <g>

Picked this from the net:
"Also, within the loop (using LIMIT), you cannot rely on cursor%FOUND to determine if the last fetch returned any rows."

But maybe this has changed over the years?

Shakepeare Received on Mon Nov 05 2007 - 15:12:45 CST