Re: Create a table only if the table doesn't exists

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 27 Jun 2006 07:29:27 -0700
Message-ID: <1151418567.698391.146270@x69g2000cwx.googlegroups.com>

DA Morgan wrote:
> Mark C. Stock wrote:
> > "DA Morgan" <damorgan_at_psoug.org> wrote in message
> > news:1151351159.811958_at_bubbleator.drizzle.com...
> > : Mark C. Stock wrote:
> > : > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > : > news:dafq92tac12l3vhip5amcsn5qu8is60pac_at_4ax.com...
> > : > : On Sat, 24 Jun 2006 14:54:03 +0200, "Joseph Balsamo" <r5tr_at_free.fr>
> > : > : wrote:
> > : > :
> > : > : >> Better but still a very bad idea. What is the business case for
> > : > : >> implementing a known bad practice of creating tables with
> > procedural
> > : > : >> code?
> > : > : >
> > : > : >Just for my information, may I ask why it is a bad practice ?
> > : > : >
> > : > :
> > : > : Databases happen to be designed. Adding tables on the fly is not a
> > : > : proper design procedure.
> > : > : You create your tables in a relational database management system.
> > : > : How can any DBA guarantee consistency, when your program is treating
> > : > : the database as a replacement for flat files, or as a replacement for
> > : > : a garbage bin?
> > : > :
> > : > : --
> > : > : Sybrand Bakker, Senior Oracle DBA
> > : >
> > : > Sybrand, have you guys every heard of installation scripts?
> > : >
> > : > ++ mcs
> > :
> > : Yes and it is irrelevant. Have you heard of CREATE SCHEMA?
> > :
> > : Running scripts != PL/SQL when it comes to DDL.
> > : --
> > : Daniel A. Morgan
> > : University of Washington
> > : damorgan_at_x.washington.edu
> > : (replace x with u to respond)
> > : Puget Sound Oracle Users Group
> > : www.psoug.org
> >
> >
> > The only thing CREATE SCHEMA adds is what you might call 'DDL rollback' --
> > still no conditional processing of DDL, just a different (and valuable) way
> > of handling a single exception (rollback DDL and quit rather than just
> > quit).
> >
> > PL/SQL provides conditional processing of DDL (i.e. exception handling), the
> > SQL language does not, and the SQL*Plus tool does not.
> >
> > If conditional processing of DDL is required, you need a procedural language
> > to manage the multiple DDL statements (which may or may not include CREATE
> > SCHEMA).
> >
> > Real world, Daniel -- not all target systems are pristeen and run by
> > conformant DBAs (and most of us don't have the power to throw non-conformant
> > DBAs into the fireswamp, so don't suggest that option). Conditional DDL is
> > actually sometimes required.
> >
> > ++ mcs

>

> Real World!

>

> Some of the largest of Oracle's customers are here in Seattle and you
> would find yourself unemployed if you built a schema using PL/SQL. That
> is real world.

Dynamic SQL is just a tool that must/should not be used for everything... however; as Mark pointed out, in some circumstances like upgrading, you have to sometimes use it in the scripts. I know ERP systems are not well liked by some DBA's but when you are developing generic upgrade scripts for a > 30000 object system with static/dynamic dependencies between different components, then having conditional processing becomes necessary.

Oracle has provided a perfectly good tool, it will be foolish not to use it when it is needed.

>

> If there is any value in creating tables using PL/SQL (and yes I'll
> grant that Oracle does in some cases such as AQ) Oracle would use it.

And they do. Open OH/rdbms/admin/initjvma.sql and look in the beginning of the script.

> I've yet to find a single line in sql.bsq or catalog.sql that would
> indicate this is a good practice.

You won't because sql.bsq is run when you issue "create database" command. dbms_sql package is created via dbmssql.sql which is run from catproc.sql which should be run after the catalog.sql script.

Regards
/Rauf Received on Tue Jun 27 2006 - 09:29:27 CDT