Re: best "drop table if exists" idiom?

From: ddf <>
Date: Tue, 21 Apr 2009 13:08:38 -0700 (PDT)
Message-ID: <>

On Apr 21, 2:36 pm, wrote:
> I'm converting some mysql scripts... what's the best oracular
> idiom for mysql's "drop table if exists" command?
> Many TIA!
> Mark
> --
> Mark Harrison
> Pixar Animation Studios

There isn't one, really. Of course you're free to write PL/SQL to do the 'dirty deed':

set serveroutput on size 1000000


          v_sqltxt varchar2(2000);
          v_success number:=0;
          v_tabname user_tables.table_name%type:=null;
          p_tabname user_tables.table_name%type:='&1';
          select table_name
          into v_tabname
          from user_tables where table_name = upper(p_tabname);
          if v_tabname is not null then
               v_sqltxt:='drop table '||v_tabname;
               execute immediate v_sqltxt;
          end if;
          when no_data_found then
                 dbms_output.put_line('Table '||p_tabname||' does not

Personally I don't mind seeing the error 'table or view does not exist' as it's not usually a script killer.

David Fitzjarrell Received on Tue Apr 21 2009 - 15:08:38 CDT

Original text of this message