Thanks for the question, Dexter.
Asked: July 22, 2022 - 3:02 pm UTC
Last updated: July 25, 2022 - 3:23 pm UTC
Version: 19.3
Viewed 1000+ times
You Asked
I'm not new to programming, but I am new to PL/SQL, and would welcome your perspective on the task I've been given.
I need to move about 50 AIX shell scripts (aka 'processes') to PL/SQL.
The processes are similar in nature in that they each call a combination of SQL*Loader, existing PL/SQL procedures, and SQL (no dynamic SQL) statements. They all share one common characteristic; they will all be called from our enterprise scheduling application (Autosys). Some are scheduled and others are event-driven.
They are not similar enough to make a central subprogram with parameters practical, although I do intend to use common/shared subprograms for routine tasks such as error logging, status logging, etc.
My initial thought was to use one package with subprograms for logging, etc. and each process in a separate procedure in the package body, but after creating the pseudo-code for the simplest process, I think this will result in a very large package body, which seems undesirable, at least from a maintenance and documentation standpoint.
I'm now thinking each process should be a separate package with another package for the common subprograms.
I would welcome your thoughts on the best approach.
Thank you for your consideration.
and Chris said...
Packages are a way of logically grouping together related functionality. Deciding which routines belong together and which should be separated is part of the art of programming!
I have no insight into the work you're doing, so can only give general pointers.
So what do
you consider the related features to be?
My initial thought was to use one package with subprograms for logging, etcOne giant package is generally a poor choice. At a minimum, I would make packages for common routines and utilities (like logging).
I'm now thinking each process should be a separate package with another package for the common subprograms.This could work and is a good starting point at least. I would look to see which entities the current processes update and try to make packages based around these.
For example, there might be several processes to load/update/remove customers/orders/invoices. In which case I'd create packages like:
customer_mgr
order_mgr
invoice_mgr
etc.
combination of SQL*LoaderNote: you can't call SQL*Loader directly from PL/SQL! Either you'll need to convert these to use external tables or use a method executing OS programs:
https://asktom.oracle.com/pls/apex/asktom.search?tag=executing-os-commands-from-plsql-code
Is this answer out of date? If it is, please let us know via a Comment