Re: Version controlling for Oracle

From: Nigel Thomas <>
Date: Mon, 28 Jan 2008 06:19:54 -0800 (PST)
Message-ID: <>


Obviously you can use any file-based version control system (RCS, SCCS, ClearCase, Synergy, PVCS, SourceSafe, CVS, etc) to control text version of your database objects.

The real fun part is deciding
1 - what is the "unit" of source control? Each table, function, package can be a separate file Make sure that your unit fits with your working practices and tools (eg SQL*Developer, TOAD etc) Make sure that you can easily extract code from database to file I recommend separating package bodies and specs I recommend keeping NOTHING in the text file except for the object definition. Everything else (including SHOW ERRORS, WHEN SQLERROR THEN FAIL etc) should go into wrapper scripts. Overall goal: avoid/minimise manual steps wherever possible 2 - how to cope with schema changes from version to version Most apps get distributed to multiple sites (even if that is just test and prod) Will you keep scripts for a "clean" or "fresh" build? Will you also keep "delta" scripts for upgrading the database? The delta scripts can be generated from tools like Oracle Designer - but they almost always need to be modified and tailored for reality

3 - how to cope with reference (seed) data changes from version to version Pretty much all applications have static or nearly static data (codes, valid values etc) which get modified from time to time. Again, you have to think about deltas versus "clean" install

4 - What happens when you upgrade a populated database? (production, for example). Upgrade steps that may work in test may explode in production Because of all these factors, planning how you are going to keep track of all the database objects, the install and upgrade scripts, and the deltas over time, is much more important than simply selecting a source control tool. Given a consistent approach, it is possible to write batch scripts to do things like:
- make a release

  • test the release (eg in overnight build tests) I've produced these in the past against version control tools like Clearcase and Synergy.

BTW there are tools other than Oracle Designer which claim to help with the diffs between database versions. I saw one blogged about quite recently on OraNA - I'll try to track it down.

Regards Nigel

  • Original Message ---- From: Syed Jaffar Hussain <> Sent: Monday, January 28, 2008 11:53:34 AM Subject: Version controlling for Oracle --
Received on Mon Jan 28 2008 - 08:19:54 CST

Original text of this message