Re: SQL possible equivalent

On May 31, 12:35 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On May 30, 11:10 am, DA Morgan <damor..._at_psoug.org> wrote:
>
>
>
> > Jeff Kish 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
>
> > The issue isn't how ... the issue is why. Oracle is NOT a Microsoft
> > product rebranded. The concepts are different, the architecture is
> > different, and kludges such as the one you are asking about used in
> > SQL Server only because of its Ingres origins are possible but never
> > done by people who learn the product.
>
> > So the answer is you can do it using Native Dynamic SQL but you should
> > instead learn Oracle unless you wish to write really really bad code.

>
> > - Show quoted text -
>
> Minor point - The first version of SQL Server was a port of Sybase to
> the Wintel platform, not Ingres. Sybase still calls its version of
> SQL: T-SQL.
>
> Jeff, look up execute immediate. Using a little PL/SQL it would be
> fairly easy to write a piece of code that could resume where a prior
> install/upgrade failed using a progress control table to track what
> succeeded, failed, etc ....
>
> You could also use a perl script to control execution of the necessary
> DDL scripts where you have a script for every object.
>
> HTH -- Mark D Powell --

Dropping and recreating tables on the fly may be a terrible kludge in a production system, but I think it is perfectly reasonable to do so in an installation script, and for example Oracle installation scripts are full of PL/SQL blocks that do this kind of thing in order to have more control over re-installations and upgrades. I second Mark's suggestion to look at using EXECUTE IMMEDIATE within PL/SQL blocks in a SQL*Plus script.

Regarding SQL vs T-SQL vs PL/SQL, I don't know how they view this in SQL Server et al, but in Oracle SQL is the query language and PL/SQL is the procedural wrapper for it, and they are two quite different things. PL/SQL is *not* Oracle's version of SQL.

btw if you can use CREATE SCHEMA then great, but it does have a couple of limitations:
http://radiofreetooting.blogspot.com/2007/02/create-schema-sql-curiosity.html Received on Thu May 31 2007 - 01:33:20 CDT