Re: simulating a temp table

Hi Scott,

Try to use set operations:

select 'DBA_TABLES' not_found from dual
union all
select 'V$PARAMETER' from dual
union all
select 'WILL_NOT_BE_FOUND' from dual
union all
select 'ALL_USERS' from dual
minus
select object_name from all_objects;

It is not very fast because of time necessary to evaluate all_objects.

                    Andrew Protasov

Scott Patterson wrote in message <34eb02c0.0_at_feed1.realtime.net>...
>I need a little help in my Oracle SQL education.
>
>My real problem is a little more contorted than this but this example uses
>all_objects which everyone will have.
>
>I have a list of objects that I need to check if they are in all_objects,
>only listing the items that are not found. This is easily done with the
>following statements but I would like to accomplish this without creating
>the tmptab table. With the SQLServers or Informix I would use a temp
 table.
>How should I be attacking this with Oracle?
>
>This will be running from a SQLPlus script.
>
>Thanks,
>Scott Patterson
>Trilogy Development Group
>scott_patterson_at_trilogy.com
>
>------
>create table tmptab (obj_name varchar2(30));
>insert into tmptab values ('DBA_TABLES');
>insert into tmptab values ('V$PARAMETER');
>insert into tmptab values ('WILL_NOT_BE_FOUND');
>insert into tmptab values ('ALL_USERS');
>select obj_name from tmptab where obj_name not in (
> select obj_name from tmptab, all_objects where obj_name = object_name);
>drop table tmptab;
>
>
>
Received on Thu Feb 19 1998 - 00:00:00 CST