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).
