From: jeffchirco on
So when my developers need to make a change to a procedure, instead of
just recompiling the procedure they want to create a new procedure
named like sp_procedure2 and then use the new procedure in their
application.
They want to do this so that they don't mess up any other application
that might be calling the same procedure. And then when they can get
around to updating the other applications they will use the new
procedure. I was wondering if anybody else does this and what you
guys think. I am against it but I am getting overruled. My database
will look confusing, source safe will be confusing, and now I have to
maintain multiple procedures when something needs to change.


From: Mladen Gogala on
On Wed, 10 Mar 2010 21:07:50 -0800, jeffchirco(a)gmail.com wrote:

> So when my developers need to make a change to a procedure, instead of
> just recompiling the procedure they want to create a new procedure named
> like sp_procedure2 and then use the new procedure in their application.

Have your developers ever heard of something called "versioning system"?
There are several of those which are widely used. The names you will most
frequently encounter are git, svn and CVS. Those things can really help
with versions, branches and revisions. There are also commercial products
which do the same thing, but with a better GUI. Personally, I think that
GUI is for wimps, especially when it comes to versioning systems. One
should learn the CVS syntax by heart and know how to diff, how to see the
revision log, check in a new version, merge 2 branches etc.



--
http://mgogala.byethost5.com
From: David Rolfe on
jeffchirco(a)gmail.com wrote:
> So when my developers need to make a change to a procedure, instead of
> just recompiling the procedure they want to create a new procedure
> named like sp_procedure2 and then use the new procedure in their
> application.
> They want to do this so that they don't mess up any other application
> that might be calling the same procedure. And then when they can get
> around to updating the other applications they will use the new
> procedure. I was wondering if anybody else does this and what you
> guys think. I am against it but I am getting overruled. My database
> will look confusing, source safe will be confusing, and now I have to
> maintain multiple procedures when something needs to change.
>
>

This whole idea is odd because it assumes the tables themselves will
never change... I wholeheartedly agree that CVS would be better, but
another alternative would be to create a new schema, create private
synonyms for all the tables and let your developers knock themselves out
with their creativity in their own padded cell....

David Rolfe
Orinda Software
Dublin, Ireland

Orinda Software make "OrindaBuild", a Java Web Service Generator for Oracle.

From: Álvaro G. Vicario on
El 11/03/2010 6:17, Mladen Gogala escribió/wrote:
> On Wed, 10 Mar 2010 21:07:50 -0800, jeffchirco(a)gmail.com wrote:
>
>> So when my developers need to make a change to a procedure, instead of
>> just recompiling the procedure they want to create a new procedure named
>> like sp_procedure2 and then use the new procedure in their application.
>
> Have your developers ever heard of something called "versioning system"?
> There are several of those which are widely used. The names you will most
> frequently encounter are git, svn and CVS. Those things can really help
> with versions, branches and revisions. There are also commercial products
> which do the same thing, but with a better GUI. Personally, I think that
> GUI is for wimps, especially when it comes to versioning systems. One
> should learn the CVS syntax by heart and know how to diff, how to see the
> revision log, check in a new version, merge 2 branches etc.

Version control cannot really help in the problem he described: I don't
think they want to push changes to the live server and then revert those
changes one month later when they learn they broke some third-party out
there. (Of course, version control is a basic tool for development anyway.)

As for the OP, I see two possibilities:

1. The procedure is modified in order to fix a bug or make an optimization
2. The procedure is modified to alter its functionality

In the first case, the appropriate tool is unit testing. The procedure
should have a bunch of test cases so they can detect if a change of
enhanment breaks backward compatibility.

In the second case, it should be carefully considered if it's possible
to add new functionality while keeping backwards compatibility. If it's
not possible, it's likely that the procedure should not replace the old
one; but it should not have a name like sp_customer_list_v2 but
something on the line of sp_customer_list_for_mailing.


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--
From: Vladimir M. Zakharychev on
On Mar 11, 8:07 am, "jeffchi...(a)gmail.com" <jeffchi...(a)gmail.com>
wrote:
> So when my developers need to make a change to a procedure, instead of
> just recompiling the procedure they want to create a new procedure
> named like sp_procedure2 and then use the new procedure in their
> application.
> They want to do this so that they don't mess up any other application
> that might be calling the same procedure.  And then when they can get
> around to updating the other applications they will use the new
> procedure.  I was wondering if anybody else does this and what you
> guys think. I am against it but I am getting overruled.  My database
> will look confusing, source safe will be confusing, and now I have to
> maintain multiple procedures when something needs to change.

[Tongue-in-cheek mode on] Switch to 11gR2 and use Edition-based
Redefinition (http://download.oracle.com/docs/cd/E11882_01/appdev.112/
e10471/adfns_editions.htm). Really powerful feature if used properly
as it allows your application code to evolve without breaking anything
(at least as described.) Hopefully, there are no serious defects in
initial implementation. :)

Now, seriously, it's nice that your developers care not to break
existing stuff, but this is one of the worst ways to evolve code I've
seen. Packages are there for implementation changes that do not affect
interfaces. They also support overloading, so if you need to change an
interface (add or remove an argument, change argument type, etc.) you
can add overloaded procedure to the package and Oracle will
automagically use the right version. And then there's testing. On a
test instance. With data set as close to production as possible.

Besides, aren't your developers aware of the scope of the changes they
introduce and possible side effects? What makes them think these
changes may break something? They should not happily clone interfaces
on every occasion just because they can and think it's safer - they
should do it only when unavoidable.

Mi dos centavos.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com