| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> PL/SQL vs Generic Dynamic SQL packages
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 packageswould 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.comReceived on Tue Dec 17 1996 - 00:00:00 CST
(317) 277-4685
Oracle DBA Eli Lilly and Company
 |
 |