Sundeep,
The biggest problems you'll have is 1) GETTING THE DEVELOPERS TO AGREE, and
2) your having to write all those procedures.
In general I prefer a mixed bag approach. If all their trying to do is
select, insert, update or delete a single row in a simple statement, then who
cares. But on the other hand if it requires a lot of database interaction to
get to the end game then the procedure is much faster. You also have the luxury
that the code gets loaded into the SGA and remains with maximum reuse, versus
those one time only statements so many developers are found of. One other
benefit, if more than one developer/application needs the same service, you get
to write it once and reuse it. Which also means you get to modify it once and
everybody is upgraded. Also you get to break it once & everybody is broken.
One problem I've found is that packages can have problems if a number of
processes attempt to execute the same package at the same time. This normally
exhibits itself as a lock time-out error, so beware. In my application the
judicious use of the DBMS_LOCK package has prevented this.
Dick Goulet
____________________Reply Separator____________________
Author: sundeep maini <[EMAIL PROTECTED]>
Date: 1/21/2002 7:05 AM
In our current environment most database interaction
is via DMLs issued from client or middle tier via
embeded SQL calls.
Is it always a good idea to go stored procedure
(packages) route?
In my opinion going with stored procedure route
isolates DB design from the upper tiers (creates an
API via stored procedure signatures making most DB
changes transparent to client least the signature is
changed).
It also offers the advantage of pre-compiled,
pre-tuned logic vs the JAVA/Client coders embedding
SQLs of their choice whereever they want.
It is also possible to use more Database facilities or
features than palin SQL alone would accord.
Finally, I can reverse engineer them into
Designer/ERWin which allows me to know impact of
cahnges on the DB.
However, in many cases of small SQLs issued directly
from Java the PL/SQL route may have overheads. Also,
the OO programmers who are usually responsible for the
app design have little experince designing DB
structures much less concerened about DB tuning which
makes it difficult to get a good seperation between
data service layer and other layers.
Can I ask the group to share their experience and/or
opinion? Are there potential downsides to stored
procedures route?
TIA
Sundeep
=====
Sundeep Maini
Consultant
Currently on Assignement at Marshfield Clinic WI
[EMAIL PROTECTED]
__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: sundeep maini
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
