Config mgmt with Oracle program units

The configuration management(CM) processes on my current project are #^%& err … not really robust. Our development platform is VB with Oracle. CM tools are PVCS Version Manager (for source code control) and Merant Tracker (for bug tracking).

Flashback 3 months. Developer Alice changes program units directly in the database using GUI tools (such as SQL*Navigator). When she is satisfied with her masterpiece, she will extract the DDL to check it in into PVCS. If she is unlucky, before the DDL is extracted, developer Bob overwrites her changes by compiling the package from DDL sitting around on disk. She notices and an entertaining squabble ensues. If she is unluckier nobody notices.

Hmm, we need a way to ensure only one developer is working on a database program unit at a time. A google search leads us to this simple solution. The main components are:

  • A database table to record locks held by a developers on program units
  • A ‘BEFORE CREATE’ trigger on the application schema. This trigger ensures that the developer who is trying to overwrite a program unit has it locked. If not, it raises an exception preventing the overwrite.

We throw in a few modifications of our own:

  • It is easier on the developers if they don’t have to separately check-out the package in the version control system (PVCS) and the database. Fortunately PVCS provides a feature called Event Triggers. This allows command line programs to be set up that are automatically invoked on configured events. The user details, filename and a host of other information can provided as parameters to this program by PVCS. We roll our own little executable that locks a program unit in the database when the corresponding file is checked-out. The program unit is unlocked on check-in.
  • Locks are based on OS username instead of the Oracle username.

Flash forward to sysdate. Alice and Bob don’t squabble any more (atleast not as frequently as they compile database program units).


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: