Re: simulating a temp table

From: Chuck Kincy <ckincy_at_pitc.com>
Date: 1998/02/18
Message-ID: <34fe628e.418422891@enews.newsguy.com>#1/1

On Wed, 18 Feb 1998 13:35:13 -0600, "Scott Patterson" <scott.patterson_at_trilogy.com> wrote:

[someone else tried:]
>>Select *
>> from all_objects
>> where obj_name not in
>>('DBA_TABLES','V$PARAMETER','WILL_NOT_BE_FOUND','ALL_USERS');
>
 

>Your query returns all the rows in all_objects that are not in the "list" of
>objects. I want the items that are in the list but not in all_objects. In
>my example, only WILL_NOT_BE_FOUND should be returned.
>

Heh. You don't have to use SQL you know. :-) Try this as a SQL*Plus script:

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( i_obj_name VARCHAR2 ) IS     SELECT object_name
    FROM all_objects
    WHERE object_name = i_obj_name;  

    i NUMBER;
    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';
 

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

        OPEN c_all_objects( tmptab(i) );
        FETCH c_all_objects INTO obj_name;
        IF c_all_objects%NOTFOUND THEN
            DBMS_OUTPUT.PUT_LINE( tmptab(i) );
        END IF;
        CLOSE c_all_objects;
 
        i := tmptab.NEXT( i );

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

I even ran it and it gave me this:

SQL> @foo
WILL_NOT_BE_FOUND   PL/SQL procedure successfully completed.  

/cpk

PL/SQL is neat! Received on Wed Feb 18 1998 - 00:00:00 CST