Re: best "drop table if exists" idiom?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 22 Apr 2009 06:00:14 -0700 (PDT)
Message-ID: <5b6a6c96-a7a0-4290-a579-46db9ef2c6ac_at_r3g2000vbp.googlegroups.com>



On Apr 21, 4:08 pm, ddf <orat..._at_msn.com> wrote:
> 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

First of all you should consider if you need to be dropping and creating tables as part of the process under conversion. Perhaps the tables in question should be replaced with Oracle temporary tables which are defined once and then only exist within a session once populated by an insert for use in a later select.

Otherwise, just code the drop and ignore errors OR perhaps remove the drop and creates and replace the create with a truncate. The tables will just be cleaned out prior to each use.

The temporary table method is best when multiple user sessions might want to run concurrently as otherwise to use a common table you would need to single thread the user sessions. Also only the owner or especially privileged users can truncate a table natively so to use truncate on non-owned tables you can write a procedure under the table owner that performs the truncate command via 'execute immediate' (PL/ SQL statement).

HTH -- Mark D Powell -- Received on Wed Apr 22 2009 - 08:00:14 CDT

Original text of this message