Re: PL/SQL vs Generic Dynamic SQL packages

The tradeoff is one of maintenance vs. performance.

Developing a set of procedures for each table means that for the busy tables, the procedure is more likely to be in the shared SQL pool and available for use without being loaded and parsed. If you use DBMS_SQL you will be generating, then parsing out a new procedure for each table each time you want to do the procedure's DML operation, impacting performance as the database gets busy.

Given an in-house (as opposed to a commercial) app, IMHO you should go with the table-specific package approach.

hth -

Regards,
jh

Joseph S. Maltese (jsmaltese_at_lilly.com) wrote:
: Your insight is needed.
 

: The Medical MDF team is attempting to evaluate the coding/maintenance
: impact and performance impact of two alternative approaches to building
: their database API. The alternatives are: Build many table specific
: PL/SQL packages versus building fewer generic packages using Dynamic
: SQL.
 
: Developer's Explanation:
: The MDF team is looking into creating generic SQL procedures/packages
: for inserts, updates, selects, and logical deletes. Currently each
: application will create an insert, update, select, and logical delete
: (i,u,s,ldl) package for each table that is used in their application (15
: tables each with 4 packages means 60 packages per application).
: For a reduction in future development time, it would make more
: sense to develop one set of i,u,s,ldl packages that would be called,
: passing the table name (and maybe other information) then creating
: dynamic SQL to process the appropriate function (i,u,s,ldl). Ideally
: there would be one set of packages (i,u,s,ldl) for the entire MDF team
: that each application could reference, passing the appropriate
: information. For future applications,only the referencing packages
: would need to be created instead of all new packages (i,u,s,ldl) for
: each table. Also, if table definitions in current applications change,
: no code changes would be needed, just a "re-parsing" of the existing
: packages.
 

: If you have any experience with these alternatives, especially as it
: relates to performance impact and code maintenance impact please
: respond.
: --
: Joseph S. Maltese
: jsmaltese_at_lilly.com
: (317) 277-4685
: Oracle DBA
: Eli Lilly and Company

--
Jared Hecker              |  ** HWA, Inc. **   Oracle and Sybase
jared_at_hwai.com            |    database architecture and administration
76276.740_at_compuserve.com  |    - serving the NYC/NJ region -