Multipurpose code in Oracle and Oracle Forms

When Oracle gave up the
ghost on client-server Forms several years ago, one of the built-in’s that went
by the wayside was related to feeding parameters into an alert message
function. If you handled errors or missing input (as you should), part of the
Forms migration process involved changing the built-in from the 6i version to
the 9i version. A complex application can have thousands of alert messages, and
a major application (Forms, that is) change can require thousands of changes. Making
a change like that was really bad form on Oracle’s part.

On the other hand, as a DBA
and developer using PL/SQL, how many times have you written
DBMS_OUTPUT.PUT_LINE(‘whatever’)? Having to write or type DBMS_OUTPUT.PUT_LINE
gets to be very tiresome, so wouldn’t it be nice to have a shorter piece of
code along the lines of a handy built-in? Maybe not so much as foresight, but
due more to good luck or realizing the same thing had to be typed over and
over, crafty Forms developers made their own built-in, in the form of a
procedure used to generate an alert message. That same construct can be used in
your everyday PL/SQL code. In fact, you can create a small message library to
take care of many types of output messages. Let’s take a look at some of these

A Simple Alert Message Procedure

Just as the section heading
says, this first method is quite simple. Suppose you have a frequent need to
output the number of records updated by a procedure, function, or block of code.
Let’s say the number of rows updated was 46. A simple “am(46);” based on the
procedure below generates the output you’re looking for.

CREATE OR REPLACE procedure am (msg number) as
dbms_output.put_line(‘Records updated: ‘||msg);

An alternative version can
handle strings, so a call to “ams(‘your message here’);” significantly shortens
your typing/number of keystrokes. When debugging or troubleshooting code/logic,
having an easily accessible “built-in” is invaluable for outputting stubs or
“where am I in the code” statements. Location reporting can confirm, for
example, where you were branched in an IF-THEN-ELSE statement. If your problem
code has lots of calls to other objects (procedures, functions, etc.), echoing
out state information such as “calling function X” and “returned from function
X” can confirm process flow. Finally, another use case involves reporting
values. You can report or track how (and even where) a variable has its value

Building an Alert Message Library

The degree of complexity or
flexibility of your message library is, of course, entirely up to you. If your
(output) messaging needs are simple, then keep the procedure simple. More
precisely, keep the number of separate procedures to a minimum. Two simple
ones, named ams and amn can be used to output messages based on string and
numeric input.

If the text component of the
output you want needs to vary based on what just took place, such as the
outcome of a DML statement, then you may want to have a set of three built-ins
(one for inserted, updated and deleted). Maybe you want to specify the type or
reason of deletion. Let’s say that one step in a batch processing job is to
count the number of duplicate records. An output of “Records counted: 46” is
useful, but then in that vein, “Duplicates counted: 46” would appear to be even
more telling. So let’s add two more built-ins.

Just like that, we’re up to
at least six separate procedures. Hints of manageability issues should be
apparent now. We were looking for something simple, but at the same time,
robust. There are at least two ways to re-simplify the desired functionality.
One way is to make the alert message procedure accept two input parameters, and
the other, which is what I’m leading up to, is to package the procedures.

Increase the number of input parameters

Again, if this simpler
solution fulfills your needs, there is no need to go further. Create the
procedure with two inputs, the first being the message text or base, the second
being the outcome, location, state or value. The two input combinations of
text/text and text/number can be boiled down to text/text if you (not Oracle)
take care of the data type conversion. Do you **have** to do the conversion?
No, but to be consistent in what you do, if you cast elsewhere, then cast here.
Regardless, the following example shows the flexibility of this first approach.

CREATE OR REPLACE procedure am (msg1 varchar2, msg2 varchar2) as

After compilation, shown
below is an example of its use.

SQL> set serveroutput on
SQL> exec am(‘Here I am’,46);
Here I am46
PL/SQL procedure successfully completed.

Well, this output could look
better (note the lack of a space between msg1 and msg2 in the output). Great, we’re
stuck with also having to format one or both message inputs to make the output
look nice. However, if nicety is not a concern, then creating messages based on
inputs like (‘dupes ‘,46), where you take care of the spacing or formatting, is
still pretty simple. Are there any pitfalls with this approach? That depends.
What if all you need is msg1 and nothing for msg2? When creating the procedure,
allow for msg2 to be null as needed. Obviously, this is not needed for msg1,

CREATE OR REPLACE procedure am
(msg1 varchar2, msg2 varchar2 default null) as
Procedure created.
SQL> exec am(‘Where am I?’);
Where am I?
PL/SQL procedure successfully completed.

Increasing the robustness

What if your message is very
long, or needs to fit on a page/line of spooled output? Ignoring word wrapping
for the time being, a simple way to chop up the text is to substring it
appropriately. This situation cries out for recursion, and of course, the best
way to understand recursion is to first understand recursion (sorry, old joke).
The chopping up process performs a length check, if the remaining string is
longer than your cutoff value, make a call to the substring-ing step, and
repeat as necessary.

What if the cut takes place
in the middle of a word? Okay, remember, this is PL/SQL, not a word processing
program/language. In a Forms environment, where the text is going into a text
field, you generally get this formatting for free. You do get some formatting functionality
via SQL*Plus, but be sure to pick the right type for your needs. Using a series
of eight strings of A through J (and a space after J), and setting the linesize
low (easier to see the difference between the word wrapping options), the
output below is an example of what takes place.

set serveroutput on format word_wrapped
exec am(insert the 8 strings here);
abcdefghij abcdefghij
abcdefghij abcdefghij
abcdefghij abcdefghij
abcdefghij abcdefghij
set serveroutput on format wrapped
exec am(insert the 8 strings here);
abcdefghij abcdefghij abcdefgh
ij abcdefghij abcdefghij abcde
fghij abcdefghij abcdefghij

The object lesson in this
example is to take advantage of what is already built in. No need to reinvent
the wheel is there? If “FORMAT WORD_WRAPPED” were not available, how would you
solve this? The second example shows the straightforward cut referenced by the
recursion approach.

Packaging up your library

There’s a good example of
how to create a base library in O’Reilly’s Oracle PL/SQL Developer’s
(by Steven Feuerstein, published in 2000), and it takes advantage
of overloading. As you may recall from a previous article, overloading allows
you to use the same name for a function or procedure (within a package), and
Oracle knows which version you’re referencing by evaluating the number and
types of parameters.

A clever naming convention
to use, particularly if you work with Java developers, is to name the package
“prt” and the procedures “ln,” so the end result is “prt.ln(‘your message’);.”
Java’s method of printing a line uses System.out.println(), so you can see why
“prt.ln” seems fairly intuitive as to its purpose and function. The examples
shown in the book handle text, numeric, date, and Boolean types of input.

CREATE or REPLACE package prt is
Procedure ln (msg in varchar2);
Procedure ln (msg in number);
Procedure ln (msg in date);
Procedure ln (msg in boolean);
END prt;

The procedures above can be
expanded to include the other examples or cases mentioned earlier. You can still
have output with boilerplate text already built in. Just add “Procedure dup(msg
in number” to the package and package body, and a call such as “prt.dup(46);”
can result in an output of “Duplicates counted: 46.”

In Closing

So what if Oracle Corporation
were to one day change/deprecate the DBMS_OUTPUT built-in? How many places in
your code base have you used this? The beauty of packaging up this
functionality into your own library is that you would only need to update one
body of code – that of what is in the package body. The Forms community spent
thousands of hours having to update thousands, if not millions, of forms binary
files because of how the alert message functionality/specification changed. For
DBAs and developers, this should serve as a valuable lesson in learning how to
take charge of what Oracle provides out of the box and making something better
to suit your own purposes.


See All Articles by Columnist
Steve Callan

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Latest Articles