Re: simulating a temp table

From: Chuck Kincy <ckincy_at_pitc.com>
Date: 1998/02/20
Message-ID: <350ee39b.517010749@enews.newsguy.com>#1/1

On Thu, 19 Feb 1998 23:26:20 GMT, ckincy_at_pitc.com (Chuck Kincy) made a complete ass of himself:

> 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;
This is stupid, try this instead:

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);
            IF found THEN 
                tmptab.DELETE(i);
            ELSE
                i := tmptab.NEXT(i);
            END IF;
        END LOOP;

    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 Fri Feb 20 1998 - 00:00:00 CST