Creating re-runnable Oracle DDL SQL Scripts

Our development team does all of their development on their local machines, databases included. When we make changes to schema's we save the SQL to a file that is then sent to the version control system (If there is a better practice for this I'd be open to hearing about that as well).

When working on SQL Server we'd wrap our updates around "if exists" statements to make them re-runnable. I am 开发者_运维技巧not working on an Oracle 10g project and I can't find any oracle functions that do the same thing. I was able to find this thread on dbaforums.org but the answer here seems a bit kludgy.

I am assuming this is for some sort of Automating the Build process and redoing the build from scratch if something fails.

As Shannon pointed out, PL/SQL objects such as Procedures, functions and Packages have the "create or replace" option, so a second recompile/re-run would be ok. Grants should be fine too.

As for Table creations and DDLs, you could take one of the following approaches.

1) Do not add any drop commands to the scripts and ask your development team to come up with the revert-script for the individual modules.

So for each create table that they add to the build, they will have an equivalent "DROP TABLE.." added to a script say."build_rollback.sql". If your build fails , you can run this script before running the build from scratch.

2)The second (and most frequently used approach I have seen) is to include the DROP table just before the create table statement and then Ignore the"Table or view does not exist" errors in the build log. Something like..

DROP TABLE EMP;
CREATE TABLE EMP (
   .......
   .......
);

The thread you posted has a major flaw. The most important one is that you always create tables incrementally. Eg, your database already has 100 tables and you are adding 5 more as part of this release. The script spools the DROP Create for all 100 tables and then executes it which does not make a lot of sense (unless you are building your database for the first time).

An SQL*Plus script will continue past errors unless otherwise configured to.

So you could have all of your scripts use :

DROP TABLE TABLE_1;
CREATE TABLE TABLE_1 (...

This is an option in PowerDesigner, I know.

Another choice would be to write a PL/SQL script which scrubs a schema, iterating over all existing tables, views, packages, procedures, functions, sequences, and synonyms in the schema, issuing the proper DDL statement to drop them.

I'd consider decomposing the SQL to create the database; one giant script containing everything for the schema sounds murderous to maintain in a shared environment. Dividing at a Schema / Object Type / Name level might be prudent, keeping fully dependent object types (like Tables and Indexes) together.