How do you work on Oracle packages in a collaborative, version-controlled environment?

Question

I'm working in a multi-developer environment in Oracle with a large package. We have a DEV => TST => PRD promotion pattern. Currently, all package edits are made directly in TOAD and then compiled into the DEV package.

We run into two problems:

1. Concurrent changes need to be promoted on different schedules. For instance, developer A makes a change that needs to be promoted tomorrow while developer B is concurrently working on a change that won't be promoted for another two weeks. When it comes promotion time, we find ourselves manually commenting out stuff that isn't being promoted yet and then uncommenting it afterwards...yuck!!!

2. If two developers are making changes at the same exact time and one of them compiles, it wipes out the other developer's changes. There isn't a nice merge; instead the latest compile wins.

What strategies would you recommend to get around this? We are using TFS for our source-control but haven't yet utilized this with our Oracle packages.

P.S. I've seen this posting, but it doesn't fully answer my question.

Answer 1

we do it with a Dev database for every stream, and labels for the different streams.

Our Oracle licensing gives us unlimited dev/test instances, but we are an ISV, you may have a different licensing option

Answer 2

We use Oracle Developer Tools for Visual Studio.NET...plugs right into TFS

Answer 3

The key is to adopt a practice of only deploying code from the source control system. I'm not familiar with TSF, but it must implement the concepts of branches, tags, etc. The question of what to deploy then falls out of the build and release tagging in the source control system.

Additional tips (for Oracle):

• it works best if you split the package spec and body into different files that use a consistent file pattern for each (e.g. ".pks" for package spec, and ".pkb" for package body). If you use an automated build process that can process file patterns then you can build all of the specs and then the bodies. This also minimizes object invalidations if you are only deploying a package body.

• put the time in to configure an automated build process that is driven from a release or build state of your source control system. If you have even a moderate number of db code objects it will pay to be able to build the code into a reference system and compare it to your qa or production system.

Answer 4

You can use the Oracle developer tools for VS or you can use sql developer. SQL developer integrates with Subversion and CVS and you can download it for free. See here: http://www.oracle.com/technology/products/database/sql_developer/files/what_is_sqldev.html

Answer 5

See my answer about Tools to work with stored procedures in Oracle, in a team (which I have just retagged).

Bottom line : don't modify procedures directly with TOAD. Store the source as files, that you will store in source control, modify then execute.

Plus, I would highly recommend that each developer works on its own copy of the database (use Oracle Express, which is free). You can do that if you store all the scripts to create the database in source control. More insight can be found here.

Answer 6

To avoid 2 developers working on the same package at the same time:

1) Use your version control system as the source of the package code. To work on a package, the developer must first check out the package from version control; nobody else can check the package out until this developer checks it back in.

2) Don't work directly on the package code in Toad or any other IDE. You have no clue whether the code you are working on there is correct or has been modified by one or more other developers. Work on the code in the script you have checked out from version control, and run that into the database to compile the package. My preference is to use a nice text editor (TextPad) and SQL Plus, but you can do this in Toad too.

3) When you have finished, check the script back into version control. Do not copy and paste code out of the database into your script (see point 2 again).

The downside (if it is one) of this controlled approach is that only one developer at a time can work on a package. This shouldn't be a major problem as long as:

• You keep packages down to a reasonable size (in terms of WHAT they do, not how many lines of code or number of procedures in them). Don't have one big package that holds all the code.
• Developers are encouraged to check out code only when ready to work on it, and to check it back in as soon as they have finished making and testing their changes.