Re: Test for the exsistance of a table

Executing a DROP TABLE statement that fails because the object does not exist is not a great burden to Oracle. You have not explained why you want to do this or the ultimate purpose, which often hampers people trying to help.

as SYSTEM

spool off
!rm droptab.sql
spool droptab.sql
select 'DROP TABLE '||owner||'.'||object_name||';' from DBA_OBJECTS

    where owner = '&1'
and

    object_type = 'TABLE'
and

    object_name like '&2%';
spool off
exit

would build a script to do that; if there are dependencies of course then either make the gen script more complex, or rerun it till they are all gone, or build a PL/SQL routine as has already been suggested and very well done if I may say so.

But again, if you want to be rid of a table, just drop the thing, Oracle isn't going to spend a fortune in CPU cycles, it either is dropped, it does not exist, or you do not have the system or owner privileges to drop it, or it is locked because of active operations involving it and an error is returned. Your test for table existence probably costs as much CPU time and other resources as just dropping the thing blindly; there must be deeper issues here I do not understand.

RSH. "Philip Morrow" <cracker_at_mymorrow.com> wrote in message news:3c5i8.78860$TV4.13070960_at_typhoon.tampabay.rr.com...
> Is there a way to test for the exsistance of a table with SQL code. I
need
> to test to see if a table exsists and if so drop it. If it doesn't the
drop
> table statement doesn't need to be executed.
>
> Thanks for the help.
>
> Phil
>
>
Received on Fri Mar 08 2002 - 16:09:01 CST