Re: simulating a temp table

David Vanidour wrote in message <34EB3D01.2EB1_at_psc-cfp.gc.ca>...
>Scott Patterson wrote:
>>
>> 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;
>
>Have you ever considered the following?
>
>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.

Scott Patterson Received on Wed Feb 18 1998 - 00:00:00 CST