Re: if exists and dropping tables

the following might work for you. you might be able to leave off the owner reference if you only have one schema or your table names are unique between schemas.

 if exists (select table_name from all_tables where owner = 'SCHEMA OWNER' and table_name = 'MY TABLE')
  begin
    drop table my_table
  end
  go

  create table my_table (...)
  ...

i am not sure what you mean about not obtaining result sets in stored procedures...

TerryTraub_at_world.std.com wrote:
>
> Hi,
> I'm doing an Oracle project after many years of Sybase and while I'm
> favorably impressed with many aspects of Oracle, I greatly miss the Sybase
> syntax for checking if a table exists:
>
> if exists (select * from sysobjects where name = 'my_table')
> begin
> drop table my_table
> end
> go
>
> create table my_table (...)
> ...
>
> This made for nice clean scripts. How do you do the equivalent
> in Oracle, to avoid getting lots of spurious error messages when
> installing tables the first time? Looks like this calls for some
> convoluted PL/SQL to check the USER_TABLES view or some such.
>
> While I'm at it... Why can't you obtain result sets using stored
> procedures in Oracle? It makes for cleaner code to keep all the SQL
> in the server, and yet this limitation forces us to use Pro*C or OO4O,
> which binds the front end code too tightly to the database schema.
> Thanks for any enlightening postings!
>
> -Terry
Received on Thu Oct 09 1997 - 00:00:00 CDT