Creating re-runnable Oracle DDL SQL Scripts

Question

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.

Doesn't help with table definitions, but many objects such as procedures, functions, packages and views, can be created with create or replace.... — Shannon Severance
– Shannon Severance, Commented Jul 19, 2011 at 16:49

Rajesh Chamarthi · Accepted Answer · 2011-07-19 19:23:07Z

2

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).

edited Jul 19, 2011 at 19:23

answered Jul 19, 2011 at 18:26

Rajesh Chamarthi

18.9k4 gold badges43 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bittersweetryan Over a year ago

This app is still in development so our scripts have a mixture of create table and alter table statements that add columns, constraints, or even redefine a columns details.

Rajesh Chamarthi Over a year ago

Even then, I think the solution would be the same. For Alter Table statements (adding columns, indexes etc..), You need not add any "DROP" statements. When you re-run the build, the alter table will fail (if it was successful for the first run..) and you'll have to ignore those errors when you look at the build-log.

Adam Musch · Accepted Answer · 2011-07-19 18:05:34Z

1

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.

answered Jul 19, 2011 at 18:05

Adam Musch

13.7k3 gold badges30 silver badges33 bronze badges

2 Comments

bittersweetryan Over a year ago

This app is still in development so our scripts have a mixture of create table and alter table statements that add columns, constraints, or even redefine a columns details.

Adam Musch Over a year ago

And that's something I believe you should change. Keep clean install DDL different from incremental update DDL.

Collectives™ on Stack Overflow

Creating re-runnable Oracle DDL SQL Scripts

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related