Re: SQL possible equivalent

From: sybrandb <sybrandb_at_gmail.com>
Date: 30 May 2007 06:34:27 -0700
Message-ID: <1180532067.305390.174450@w5g2000hsg.googlegroups.com>

On May 30, 3:10 pm, Jeff Kish <jeff.k..._at_mro.com> wrote:
> I have a script for ms sql server that checks to see if a table exists... if
> it does it drops it and recreates it.
>
> I'm wondering how to do this using only sql if possible. I can figure it out
> using pl/sql but I'm not sure of the correct approach (or even if it is
> possible) using just sql.
>
> I realize you can select from user_tab_cols like this:
> select table_name from user_tab_columns where upper(table_name) = 'ASSET'
>
> but how do I fit this into a sort of ifdef else sort of flow control in sql?
>
> Can someone point me in the right direction? Do I need to somehow insert
> pl/sql into the sql?
>
> thanks
> Jeff Kish

Please try to forget any of the Mickeysucks kludges you learned when using sqlserver.
The typical approach of Oracle is

whenever sqlerror continue -- the default drop table foo;

create table foo;

exit

If table foo doesn't exist, the statement will fail and Oracle will continue to create it.
If the table does exist, it will be dropped.

If you insist in creating a non-scalable application you would need to

declare dummy varchar2(1);
begin

    begin
    select 'x' into dummy
    from dual
    where exists
    (select 'x'

     from user_tables
     where table_name='foo'
     );
     exception when no_data_found then dummy := '';
     end;
    if dummy = 'x' then
      execute immediate 'drop table foo';

    end if;
    execute immediate 'create table foo';   end;
/

Please take a piece of paper now and write 100 times 'I need to stop trying to turn Oracle in sqlserver or I will end up in hell'

--
Sybrand Bakker
Senior Oracle DBA

Received on Wed May 30 2007 - 08:34:27 CDT