Re: SQL possible equivalent

On May 30, 9:34 am, sybrandb <sybra..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

There is generally no valid reason to drop and recreate tables in production code. If you need a work table then you either use a permanent table that is truncated either directly before or after each use or a global temporary table which is defined only once and used when needed.

The need for work tables is itself usually questionable. The nonblocking  nature of the Oracle locking and read consistency model plus the ability of Oracle to handle complex queries generally allows removing work tables from the process.

HTH -- Mark D Powell -- Received on Wed May 30 2007 - 09:15:14 CDT