Back in January, I received this note from a new PL/SQL developer:
I’ve joined the wonderful world of PL/SQL. This is my very first coding effort with it. I’ve done a lot of programming in COBOL, Fortran and such in the 70’s and 80’s. Now, I’m totally confused with PL/SQL program structure. I’m looking for a clean design approach to do the following:
Will need to generate/write multiple files to a directory.
Q1. What would be the best structure to do this? Develop a package for each file, or create one package with a procedure for each file?
Q2. Don’t see many examples regarding writing files.
Any advice or example on structure would be helpful.
Here is my advice:
I, too, was a Fortran programmer way back when - from about 1979 to 1987. Ah, and then I "fell into" a job with Oracle and from there on, it's been All PL/SQL, All the Time.
I tell you this as a way of saying it's a bit hard for me to remember Fortran and thereby sense why you might have trouble with PL/SQL program structure. I am certain there were modules (procedures and functions), but probably not packages (these came from Ada).
Still, ignorance never stopped me from giving my opinion, so here goes:
A1. Do not write a separate package for each file. Instead, create a package that contains more generic UTL_FILE-based utilities, which you can then "apply" to any and all files with which you need to work.
And consider that a starting point. Build or find (more on that below) a whole set of utility packages: string manipulation, date management, error logging, execution tracing. Don't reinvent the wheel! The PL/SQL ecosystem is not nearly as rich as some others when it comes to publicly available reusable "stuff", but it is not a desert, either. And so on to:
A2. I offer my demo.zip download, which contains lots and lots of demonstration code and a few utility packages you might find useful, including:
Provides some "helper" programs on top of UTL_FILE.
Calculate elapsed time of program execution
Calculate amount of PGA memory consumed by session
Helper package for the PL/Scope feature
Parse delimited strings into a collection
Helps you avoid runaway/infinite loops
Another UTL_FILE helper package
Very basic error management package
Helper package for the LOG ERRORS feature
Parse contents of DBMS_UTILITY.FORMAT_CALL_STACK
Implements assertion routines for PL/SQL
Keep track of names that you've used, to avoid reuse.
Another version of string parsing package
Helps you parse out placeholders for dynamic SQL method 4 scenarios
Enhancements for UTL_FILE and more general file management, using some Java classes.
Help you utilize top down design and nested modules to build programs
WARNING! This code is definitely not all ready for production. YOU must test it and verify it works for you. I am NOT responsible for any bugs. Etc., etc.
Finally, back to UTL_FILE: download the demo.zip and search for files containing UTL_FILE. You will find lots and lots of examples.
Just remember that some Oracle features like UTL_FILE are known security risks - and some tools like Toad DB Health Check even suggest turning their use off. Here's just one of many examples out here about how this utility can present a security risk: www.red-database-security.com/.../oracle-directory-traversal-via-utl_file.html
I only point this out because sometimes people will think if there's a feature or utility in Oracle (or any major software) then it's OK to use it. It is - if you know about all its little quirks and possible security issues :)
Thanks, Bert. Very helpful info. Of course, this just points out the fundamental difference between us. I am an optimistic guy, always believing that my fellow humans would never want to do evil. You, on the other hand, are....so much more sensible.