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 VARCHAR2The implementation uses the EXECUTE IMMEDIATE statement in a cascading IF statement. Here is a portion of the function body:
ELSIF nargs_in = 2THEN EXECUTE IMMEDIATE v_code || '(:1,:2); END;' USING OUT retval, arg1_in, arg2_in;ELSIF nargs_in = 3THEN 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-99175921860444161020The 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.
15.6 NDS Utility Package
To make it easier for you to take advantage of the various generic utilities discussed in this chapter, I have created a single package called ndsutil. This package, available from the O'Reilly site in ndsutil.pkg, contains the programs listed in Table 15-1.
| Table 15-1. Contents of the NDS utility package on the O'Reilly site | |
| Name | Description |
| execImmed | A substitute for EXECUTE IMMEDIATE that does not need a USING or INTO clause; includes error handling. |
| openFor | A substitute for OPEN FOR that does not need a USING clause; includes error handling. |
| showCol | Shows the contents of a single column in the specified table. |
| tabCount | Returns the number of rows in the specified table, with an optional WHERE clause. |
| countBy | Returns the number of rows in the specified table for a particular GROUP BY expression, with an optional HAVING clause. |
| dynPLSQL | Executes a dynamic PL/SQL string, automatically making sure that it is a valid block and that it ends in a semicolon. The USING clause is not allowed. |
The package is defined using the invoker rights mode (AUTHID CURRENT_USER). This means that no matter who owns the package, any external references in the dynamic SQL you execute via ndsutil are resolved according to the authority of the invoking schema, not the owner.
All programs contain exception sections that display the error and the offending SQL. Procedures then re-raise the error, whereas functions generally return NULL or a NULL/empty structure.
