Re: PL/SQL vs Generic Dynamic SQL packages

Hi,

I am in the similair situation as you do. I successfully write up a generic package in creating the PL/SQL statement(The sql can be run in sqlplus), but experience difficulty in running the script with DBMA_execute in database.

I may get you the details if you are interested.

David Ng

Joseph S. Maltese 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
  Received on Tue Dec 17 1996 - 00:00:00 CST