Re: DBMS_ERRLOG will not compile within a cursor loop

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 01 Nov 2007 13:49:43 -0700
Message-ID: <1193950181.169048@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

Received on Thu Nov 01 2007 - 15:49:43 CDT