15.5.3.1 Dramatic code reduction

This is a true story, I kid you not. During a consulting stint at an insurance company here in Chicago, I was asked to see what I could do about a particularly vexing program. It was very large and continually increased in size—soon it would be too large to even compile. Much to my amazement, this is what the program looked like:

CREATE OR REPLACE PROCEDURE process_line (line IN INTEGER)

IS

BEGIN

IF line = 1 THEN process_line1;

ELSIF line = 2 THEN process_line2;

...

ELSIF line = 514 THEN process_line514;

...

ELSIF line = 2057 THEN process_line2057;

END IF;

END;

Each one of those line numbers represented fine print in an insurance policy that helped the company achieve its primary objective (minimizing the payment of claims). For each line number, there was a "process_line" program that handled those details. And as the insurance company added more and more exceptions to the policy, the program got bigger and bigger. Not a very scalable approach to programming!

To avoid this kind of mess, a programmer should be on the lookout for repetition of code. If you can detect a pattern, you can either create a reusable program to encapsulate that pattern, or you can explore the possibility of expressing that pattern as a dynamic SQL construction.

At the time, I fixed the problem using DBMS_SQL, but dynamic SQL would have been a perfect match. Here's the NDS implementation:

CREATE OR REPLACE PROCEDURE process_line (line IN INTEGER)

IS

BEGIN

EXECUTE IMMEDIATE

'BEGIN process_line' || line || '; END;';

END;

From thousands of lines of code down to one executable statement! Of course, in most cases, identification of the pattern and conversion of that pattern into dynamic SQL will not be so straightforward. Still, the potential gains are enormous.

15.5.3.2 Generic calculator function

Suppose I have to build a GUI application that allows users to select their calculation of choice, enter the arguments, and then display the results. There are a dozen different calculations, accepting from one to five arguments, all returning a single value.

I could write a separate screen for each calculation. However, that approach is not only labor-intensive, but also high-maintenance. Every time a new calculation is added to the mix, I have to go in and write another screen. Yuck! Wouldn't it be nice if I could softcode my application so that when users need access to another calculation, they can essentially add it themselves?

So I build a set of database tables to store header-level information about the calculation, including a description, the name of the calculation function, the number of arguments, descriptions of each argument, and so forth. But now I need a utility that will run any of the calculations I send to it. This is where dynamic PL/SQL comes into play.

Here is the header of a function that accepts up to five arguments and runs whatever function is requested:

/* File on web: dyncalc.sf */

CREATE OR REPLACE FUNCTION dyncalc (

oper_in IN VARCHAR2,

nargs_in IN INTEGER := 0,

arg1_in IN VARCHAR2 := NULL,

arg2_in IN VARCHAR2 := NULL,

arg3_in IN VARCHAR2 := NULL,

arg4_in IN VARCHAR2 := NULL,

arg5_in IN VARCHAR2 := NULL

)

RETURN VARCHAR2

The implementation uses the EXECUTE IMMEDIATE statement in a cascading IF statement. Here is a portion of the function body:

ELSIF nargs_in = 2

THEN

EXECUTE IMMEDIATE v_code || '(:1, :2); END;'

USING OUT retval, arg1_in, arg2_in;

ELSIF nargs_in = 3

THEN

EXECUTE IMMEDIATE v_code || '(:1, :2, :3); END;'

USING OUT retval, arg1_in, arg2_in, arg3_in;

No rocket science here. But it gets the job done, as shown in this SQL*Plus session:

SQL> BEGIN

2 DBMS_OUTPUT.PUT_LINE (dyncalc('sysdate'));

3 DBMS_OUTPUT.PUT_LINE (dyncalc('power', 2, 2, 44));

4 DBMS_OUTPUT.PUT_LINE (

5 dyncalc ('greatest', 5, 66, 5, 88, 1020, -4));

6 END;

7 /

05-MAY-99

17592186044416

1020

The dyncalc function has some design limitations, but these can be addressed by shifting from a series of hardcoded parameters (arg1_in through arg5_in) to a single array to pass in all argument values. This technique is demonstrated in the dyncalc.pkg file available from the O'Reilly site.

You will find an even more interesting example of dynamic PL/SQL in the str2list.pkg. This package offers a very generic string-parsing engine that parses any delimited string and deposits the individual items into the collection of your choice.