Re: PL/SQL or SQL in the middle tier?

On Feb 20, 2:10 pm, The House Dawg <mhous..._at_ix.netcom.com> wrote:

Hi Matt,

> I work for a Fortune 50 company and we currently have millions of PL/
> SQL code in production. Recently, there has been an initiative
> launched by our DBA's to have software engineers move the PL/SQL out
> of the database and move it into the middle tier.
>

Out of curiosity, what were the reasons given?

> Does this make sense to anyone?

It depends on the reasons given, but I have never had any luck with it.

I realize that SOX could be playing a
> part in this because it's difficult to debug a production issue when
> developers must be granted temporary execute privilege on the various
> stored procedures and stored functions to debug the issue.

That shouldn't be an issue with proper change control (integration test matches production, and if not, that is fixed very quickly :)).

>
> I can think of many reasons *NOT* to do this.
>

You and me both. I have *never* seen database access code perform better in the middle tier, and the issues surrounding consistentsy of business rules is horrific. Just this week, we found several duplicate rows in a table that weren't supposed to have any, but the app was supposed to manage that...

I'm not saying you can't put constraints in the database and still access it from the middle tier, but it is almost never (I have never seen it) as good performance wise.

I truly am curious as to the reasons a DBA, of all people, would push to get code out of the database.

Regards,

Steve Received on Wed Feb 20 2008 - 14:21:03 CST