Author: Steve Callan, stevencallan@hotmail.com
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 possibilities.
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
begin
dbms_output.put_line('Records updated: '||msg);
end;
/
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 changed.
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
begin
dbms_output.put_line(msg1||msg2);
end;
/
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, right?
CREATE OR REPLACE procedure am
(msg1 varchar2, msg2 varchar2 default null) as
begin
dbms_output.put_line(msg1||msg2);
end;
/
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 Workbook (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.
