Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Create a table only if the table doesn't exists

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

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 26 Jun 2006 15:54:22 -0700
Message-ID: <1151362470.943915@bubbleator.drizzle.com>


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.

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. I've yet to find a single line in sql.bsq or catalog.sql that would indicate this is a good practice.

-- 
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
Received on Mon Jun 26 2006 - 17:54:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US