| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re:Backend Architecture in 3 tier environment
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 <sundeep_maini_at_yahoo.com> 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
mainis_at_mfldclin.edu
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: ListGuru_at_fatcity.com (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).
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: ListGuru_at_fatcity.com (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). Received on Mon Jan 21 2002 - 10:46:28 CST
 |
 |