Re: simulating a temp table

From: Chuck Kincy <ckincy_at_pitc.com>
Date: 1998/02/19
Message-ID: <350cb6e8.505565490@enews.newsguy.com>#1/1

On Thu, 19 Feb 1998 15:38:33 -0800, David Vanidour <dvanidou_at_psc-cfp.gc.ca> wrote:

>Nice bit of PL/SQL there Chuck.
>

Thanks...it's a delightful little language.

Thought I'd add that if all_objects is really huge it might be better to reverse the logic of the loop....eliminate the where constraint on the cursor and just go through all lines of all_objects and zap things out of the array when they show up in the table.

Funny thing is, this actually takes *longer* on my system...there must be an index on object_name or something interesting.

Here's the code....

set serveroutput on size 1000000  

DECLARE
    TYPE tmptab_t IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;     tmptab tmptab_t;  

    CURSOR c_all_objects IS
    SELECT object_name
    FROM all_objects;  

    i         NUMBER;
    found     BOOLEAN;

    obj_name all_objects.object_name%TYPE; BEGIN

    tmptab(0) := 'DBA_TABLES';
    tmptab(1) := 'V$PARAMETER';
    tmptab(2) := 'WILL_NOT_BE_FOUND';
    tmptab(3) := 'ALL_USERS';
 
    FOR r in c_all_objects LOOP
        found := FALSE;
        i := tmptab.FIRST;
        WHILE i IS NOT NULL AND NOT found LOOP
            found := r.object_name = tmptab(i);
            i := tmptab.NEXT(i);
        END LOOP;
        IF found THEN
            tmptab.DELETE(i);
        END IF;

    END LOOP;       i := tmptab.FIRST;
    WHILE i IS NOT NULL LOOP

        DBMS_OUTPUT.PUT_LINE( tmptab(i) );
        i := tmptab.NEXT( i );

    END LOOP;
EXCEPTION
    WHEN OTHERS THEN NULL;
END;
/

/cpk Received on Thu Feb 19 1998 - 00:00:00 CST