Backend Architecture in 3 tier environment

From: sundeep maini <sundeep_maini_at_yahoo.com>
Date: Mon, 21 Jan 2002 07:49:14 -0800
Message-ID: <F001.003F5CD0.20020121070532@fatcity.com>

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

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: sundeep_maini_at_yahoo.com

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 - 09:49:14 CST