Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: simulating a temp table
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');
Received on Wed Feb 18 1998 - 00:00:00 CST