Re: best "drop table if exists" idiom?

From: ddf <oratune_at_msn.com>
Date: Tue, 21 Apr 2009 13:08:38 -0700 (PDT)
Message-ID: <c51e53dc-a2dd-4317-a4b2-c2fb90a969c4_at_s20g2000vbp.googlegroups.com>

On Apr 21, 2:36 pm, m..._at_pixar.com 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

declare

          v_sqltxt varchar2(2000);
          v_success number:=0;
          v_tabname user_tables.table_name%type:=null;
          p_tabname user_tables.table_name%type:='&1';
begin
          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;
exception
          when no_data_found then
                 dbms_output.put_line('Table '||p_tabname||' does not

exist');
end;
/

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