Re: Worst of the worst

From: Adrian Billington <billiauk_at_yahoo.co.uk>
Date: 30 May 2003 05:49:18 -0700
Message-ID: <dee17a9f.0305300449.2052a6f7@posting.google.com>

> > Had they not gotten it correctly twice in the same block I'd be more forgiving. But this? Obviously no one
> > ever tried this example before including it in the docs.

Looking at the 8.1.7 docs, it becomes obvious that the example Daniel posts is a hangover from earlier documentation. Below is the 817 version, where whoever wrote the block had included a forward-declared procedure called get_time. If you take a look at the way it is written, you would assume that the Oracle guys were embarrassed by the fact that whoever wrote it had never heard of dbms_utility, so they tried to cover it up in the 9.2 docs but were not thorough enough to remove all traces of earlier poor code.

 1 DECLARE

 2     TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
 3     TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
 4     pnums  NumTab;
 5     pnames NameTab;
 6     t1 NUMBER(5);
 7     t2 NUMBER(5);
 8     t3 NUMBER(5);
 9     PROCEDURE get_time (t OUT NUMBER) IS
10     BEGIN SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual; END;
11  BEGIN
12     FOR j IN 1..5000 LOOP  -- load index-by tables
13        pnums(j) := j;
14        pnames(j) := 'Part No. ' || TO_CHAR(j); 
15     END LOOP;
16     get_time(t1);
17     FOR i IN 1..5000 LOOP  -- use FOR loop
18        INSERT INTO parts VALUES (pnums(i), pnames(i));
19     END LOOP;
20     get_time(t2);
21     FORALL i IN 1..5000  -- use FORALL statement
22        INSERT INTO parts VALUES (pnums(i), pnames(i));
23     get_time(t3);
24     DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
25     DBMS_OUTPUT.PUT_LINE('---------------------');
26     DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR(t2 - t1));
27     DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR(t3 - t2));

28* END;
SQL> /
Execution Time (secs)

---

FOR loop: 32
FORALL: 3 Link:-

http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/04_colls.htm#1059

Regards
Adrian Received on Fri May 30 2003 - 07:49:18 CDT