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

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Mon, 26 Jun 2006 16:10:20 -0400
Message-ID: <F-CdnSswEL2zoj3ZnZ2dnUVZ_vqdnZ2d@comcast.com>

"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 Received on Mon Jun 26 2006 - 15:10:20 CDT