Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

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, etc

One 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*Loader

Note: 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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library