Re: Table Existence

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 21 Feb 2000 20:53:54 -0500
Message-ID: <2vq3bsgv49g7lnenqvigffg3kqra30te5b@4ax.com>

A copy of this was sent to "Louis Frolio" <frolio_at_videoshare.com> (if that email address didn't require changing) On Mon, 21 Feb 2000 18:04:29 -0500, you wrote:

>Is there a sql command in Oracle that will tell me whether or
>not a table exists? In T-SQL the command "IF EXISTS(..)" does
>just this. Any help would be appreciated.
>
>L
>

You are probably trying to create a script to drop and then create new tables. It would be easiest to just 'drop' the table and ignore the error in sqlplus but if you want to drop only when it exists:

In Oracle8.0 and before you could create procedure like:

create or replace procedure execute_immediate( p_sql in varchar2 ) is

    cursor_name     pls_integer default dbms_sql.open_cursor;
    ignore          pls_integer;

BEGIN
    dbms_sql.parse(cursor_name, p_sql, dbms_sql.native);     ignore := dbms_sql.execute(cursor_name);     dbms_sql.close_cursor(cursor_name); END;
/

and then you can:

begin

   for x in ( select * from dual

               where exists ( select * from user_tables 
                               where table_name = 'MYTABLE' ) ) 
   loop
      execute_immediate( 'drop table mytable' );

   end loop;
end;
/

(see http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html if you get insufficient priv error or object does not exist error). You would probably turn the above into a small procedure so you can just call:

exec drop_table( 'MYTABLE' )

In Oracle8i, release 8.1 and up you can just:

begin

   for x in ( select * from dual

               where exists ( select * from user_tables 
                               where table_name = 'MYTABLE' ) ) 
   loop
      EXECUTE IMMEDIATE 'drop table mytable';

   end loop;
end;
/

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Feb 21 2000 - 19:53:54 CST