Re: simulating a temp table

Nice bit of PL/SQL there Chuck.

Chuck Kincy wrote:
>

[SNIP] # evidence of my poor ill thought out effort removed.

> 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 Thu Feb 19 1998 - 00:00:00 CST