DBMS_ERRLOG will not compile within a cursor loop

#11

Ken Denny wrote:

Quote:

On Nov 1, 3:49 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
Ken Denny wrote:
On Nov 1, 11:26 am, DA Morgan <damor... (AT) psoug (DOT) 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 ....

Sorry. I didn't mean to imply that the savings was never more than a
few microseconds. There are many times where the savings using bulk
collect and avoiding cursor loops are enormous. What I meant to say
was that *SOMETIMES* the savings are minimal while the increase in
keystrokes are huge. Consider in your example if your insert had to
reference 100 or more individual columns and your parent table had no
more than 100 rows.

The number of extra keystrokes if fixed:

FETCH cursor1 INTO record1
replaced with
FETCH r BULK COLLECT INTO l_data LIMIT 1000;
and the limit clause is optional.

INSERT INTO child (col1, col2, col3) VALUES (val1, val2, val3)
rather than
FORALL i IN 1..l_data.COUNT
INSERT INTO child VALUES l_data(i);

I think you will find it actually saves keystrokes.

But to assume that milliseconds don't matter is to assume that
you have a single user system and that the amount of time
latches are held doesn't affect other activities on the system.
I wouldn't make that assumption.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DBMS_ERRLOG will not compile within a cursor loop

comp.databases.oracle.misc comp.databases.oracle.misc

Re: DBMS_ERRLOG will not compile within a cursor loop - 11-07-2007 , 03:09 PM