Tools to work with stored procedures in Oracle, in a team ?

Question

What tools do you use to develop Oracle stored procedures, in a team :

• To automatically "lock" the current procedure you are working with, so nobody else in the team can make changes to it until you are finished.
• To automatically send the changes you make in the stored procedure, in an Oracle database, to a Subversion, CVS, ... repository

Thanks!

Answer 1

A relatively simple (if slightly old-fashioned) solution might be to use a "locking" rather than "merge" mode version control system.... Subversion or CVS generally use a "merge" mode (although I believe Subversion can be made to "lock" files?)

"Locking" mode version control systems do have their own drawbacks of course.....

The only way I can think of doing in in Oracle might be some of of BEFORE CREATE TRIGGER, maybe referencing a table to look-up who can run a package in. Sounds a bit nasty though?

Answer 2

Using Oracle SQL Developer 1.5, you can easily create and manage connections to CVS or Subversion. To create a CVS connection (for example), click Versioning -> CVS -> Check out Module. You will run through a wizard to create the connection (host, username, etc), then you can check your procedures/functions out and in as normal.

Integration with CVS is also provided in Toad.

Answer 3

Toad also does this without requiring CVS / SVN.

Answer 4

Treat PL/SQL as usual code : store it in files, and manage these files with your revision control tool and your internal procedures.

If you do not already have a revision control tool, then write your requirements down and pick one up. A lot of people it seems use Subversion, associated to TortoiseSVN as a client on Windows (I do).

The thing is : use your tool as is recommended, and adapt your procedures accordingly. For instance, Subversion uses a copy-modify-merge model by default, as opposed to a lock-modify-unlock model which you seem to favor.

In my case, I like to use TortoiseSVN, as stated above. And as is usual with this tool :

• I never lock any files. This is very manageable with small teams, and it requires ahead planning on larger ones, which is always a good thing IMHO.
• I send my changes manually back to the server, because ... I don't think there's another way with Subversion (plus, internal procedures forbid a commit without a message, which is also a good thing IMHO).

And whatever your choice, I recommend reading this post (and related ones) about database versioning.

Answer 5

You may also want to look at Aqua Data Studio. They have built in SVN as well and is a great Stored Proc editor.

Answer 6

I'm not sure if the original poster is still monitoring this, but I'll ask the question anyways.

The original post requested to be able to:

To automatically "lock" the current procedure you are working with, so nobody else in the team can make changes to it until you are finished.

Perhaps the problem here is one of development paradigm more than the inability of a product to "lock" the stored proc. Whenever I hear "I want to lock this so noone else changes it" I immediately get the feeling that people are sharing a schema and everyone is developing in the same space.

If this is the case, why not simply let everyone have their own schema with a copy of the data model? I mean seriously folks, it doesn't "cost" anything to create another schema. That way, each developer can make changes until they're blue in the face without affecting anyone else.

Another trick I've used in the past (on small teams) when it wasn't feasible to let every developer have their own copy of the data because of size, was to have a master schema with all the tables and code in it, with public synonyms pointing to it all. Then, if the developer wants to work on a stored proc, he simply creates it in his schema. That way Oracle name resolution finds that one first instead of the copy in the master schema, allowing them to test their code without affecting anyone else. This does have it's drawbacks, but this was a very specific case where we could live with them. I would NEVER implement something like this in production obviously.

As for the second requirement:

To automatically send the changes you make in the stored procedure, in an Oracle database, to a Subversion, CVS, ... repository

I'd be surprised to find tools out there smart enough to do this (perhaps an opportunity :). It would have to connect to your db, query the data dictionary (USER_SOURCE) and pull out the associated text. A tall order for source control systems where are almost universally file based.

Answer 7

After searching for a tool to handle version control for Oracle objects with no luck we created the following (not perfect but suitable) solution:

1. Using dbms_metadata package we create the metadata dump of our Oracle server. We create one file per object, hence the result is not one huge file but a bunch of files. For recognizing deleted object we delete all the files before creating the dump again.
2. We copy all the files from the server to the client computer.
3. Using Netbeans we recognize the changes, and commit the changes to the CVS server (or check the diffs...). Any CVS-handler software would work here, but we were already using Netbeans for other purposes. And Netbeans also allows to create an ant task for calling the Oracle process mentioned in step 1, copying the files mention in step 2...

Here is the most imporant query for step 1:

SELECT object_type, object_name, 
  dbms_metadata.get_ddl(object_type, object_name) object_ddl FROM user_objects
WHERE OBJECT_TYPE in ('INDEX', 'TRIGGER', 'TABLE', 'VIEW', 'PACKAGE', 
  'FUNCTION', 'PROCEDURE', 'SYNONYM', 'TYPE')  
ORDER BY OBJECT_TYPE, OBJECT_NAME

One file per object approach helps to identify the changes. If I add a field to table TTTT (not a real table name of course) then only TABLE_TTTT.SQL file will be modified.

Both step 1 and step 3 are slow processes. (several minutes for a few thousand of files)