Re: Drop table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 21 Feb 2000 08:52:24 -0500
Message-ID: <pjg2bsg87bi44v4sd22m2s0hg21398c4pe@4ax.com>

A copy of this was sent to petr_kucera_at_my-deja.com (if that email address didn't require changing) On Mon, 21 Feb 2000 10:10:46 GMT, you wrote:

>Is there a way how to drop a table in Oracle DB, but I'd like to check
>if this table alredy exists in database?
>Something like this:
>
>if exists(select * from user_tables where table_name='mytable') then
> drop table mytable;
>end if;
>
>but I can't use DDL statements.
>
>ThanX.
>
>

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)

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;
/

>
>
>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
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 - 07:52:24 CST