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

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 2

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.