Strategy for managing Oracle packages without breaking code

Question

I'm curious to find out how people manage their packages in their applications.

For example, in our development instance, an application developer may want a change to a stored procedure. However, changing the stored procedure will break the existing Java code until the DAO layer is updated to accommodate for the changes.

My typical practice has been to put the new procedure implementation into a "DEV" package. The developer can then change his reference to this package, do his testing and then when we're ready, we can replace the procedure in the "production" package, delete it from DEV and the developer changes his reference back to the production package.

However, I'm finding it doesn't work as swimmingly as I'd like. First, if there's a bunch of Java code which depends on the DEV package, then I'm in the same situation as if were editing the production package directly - if I break the package, I'll break a bunch of code.

Second, people get busy and we don't get around to moving the package into production as soon we should. Then we have two versions of the stored procedure floating around and it gets difficult to remember what has been moved into production and what hasn't.

The goal is to keep the developers working. Yes, it's a development server, but we don't want to be breaking code unexpectedly.

Can anyone suggest methodologies that have worked for them to address this issue?

Answer 1

If each developer has their own schema in the database and there are public synonyms for all objects in the shared schema and all the Java code uses non-qualified object names, then a local copy of the package in a particular developer's schema will have precedence over the shared version. So developer A can take the current version of the package, install it in his or her local schema, make whatever changes are desired to the package, and make whatever Java changes are necessary all within their own development environment (I'm assuming that developers have their own local app server). When both sets of changes are sufficiently stable that they can be checked in to the shared development environment, both the PL/SQL package and the Java changes can be built out to the shared development environment (the shared development app server and the real schema in the development database). The developer can then drop their local copy of the package.

That approach works reasonably well so long as the developers are checking the PL/SQL out of source control to start their changes rather than assuming that whatever local copy they have in their schema is current-- if developers keep old, local versions of code around in their local schema, they may end up with difficult to debug issues where their PL/SQL and Java versions are out of sync. You can resolve that problem by automating processes that, for example, drop packages from developer schemas if they haven't been modified in a reasonable period of time and if those packages aren't checked out by the developer in source control or by building scripts that let a developer automate the refresh of their schema as part of the build process.

Answer 2

The Java/DAO layer should only be affected if the procedure specification changes (ie number, name etc of parameters). Mitigation strategies for this are

1. Add new parameters with DEFAULT values for parameters so that they don't need to be passed.
2. Don't change the order of parameters if they cat called positionally [eg pkg.proc_a (1,2,3)], or rename them if called by name [eg pkg.proc_b (p_1 => 1, p_2 => 2)]

3. Use packages for procedures and functions so you can overload them

   create or replace pkg is proc (p1 in varchar2); proc (p1 in varchar2, p2 in number); end;

With overloading you can have multiple procedures with the same name in a package just with different numbers and/or datatypes of the parameters

11gR2 has introduced Editioning to solve this problem. It allows multiple versions of packages and the application code choose which 'edition' (version) of the code it wants to see - the default 'base' edition or a development version.

However I suspect upgrading the database version isn't a practical solution.