#

| Chapter 4 Native Dynamic SQL in Oracle8i |

## 4.6 Building Applications with NDS

By now, you should have a solid understanding of how native dynamic SQL works in PL/SQL. This section covers some topics that you should be aware of as you start to build production applications with this new PL/SQL feature.

### 4.6.1 Sharing NDS Programs with Invoker Rights

I have created a number of useful generic programs in my presentation on NDS, including functions and procedures that do the following:

- Execute any DDL statement

- Return the count of rows in any table

- Return the count for each grouping by specified column

These are pretty darn useful utilities and I want to let everyone on my development team use them. So I compile them into the COMMON schema and grant EXECUTE authority on the programs to PUBLIC.

However, there is a problem with this strategy. When Sandra connects to her SANDRA schema and executes this command:

>     SQL> exec COMMON.execDDL ('create table temp (x date)');

she will inadvertently create a table in the COMMON schema -- unless I take advantage of the invoker rights model described in Chapter 3, Invoker Rights: Your Schema or Mine?

The invoker rights model means that you define your stored programs so they execute under the authority, and with the privileges, of the invoking schema rather than the defining schema (which is the default in Oracle 8.1 and the only option prior to Oracle 8.1).

Fortunately, there isn't much you have to do to take advantage of this new feature. Here is a version of execDDL that executes any DDL statement -- but always having an impact in the calling or invoking schema:

>     CREATE OR REPLACE PROCEDURE execDDL (ddl_string IN VARCHAR2)
>        AUTHID CURRENT_USER
>     IS
>     BEGIN
>        EXECUTE IMMEDIATE ddl_string;
>     END;
>     /

I recommend that you use the AUTHID CURRENT_USER clause in all of your dynamic SQL programs, particularly in those that you plan to share among a group of developers. The package discussed at the end of this chapter in Section 4.7, "NDS Utility Package" follows this standard.

### 4.6.2 Error Handling

Any robust application needs to anticipate and handle errors. Error detection and correction with dynamic SQL can be especially challenging.

Sometimes the most challenging aspect to building and executing dynamic SQL programs is getting the string of dynamic SQL correct. You might be combining a list of columns in a query with a list of tables and then a WHERE clause that changes with each execution. You have to concatenate that stuff, getting the commas right, the ANDs and ORs right, and so on. What happens if you get it wrong?

Well, Oracle raises an error. And this error usually tells you exactly what is wrong with the SQL string, but that information can still leave lots to be desired and figured out. Consider the following nightmare scenario: I am building the most complicated PL/SQL application ever. It uses dynamic SQL left and right, but that's OK. I am a pro at the new NDS. I can, in a flash, type EXECUTE IMMEDIATE, OPEN FOR, and all the other statements I need. I blast through the development phase. I also rely on some standard exception-handling programs I have built that display an error message when an exception is encountered.

Then the time comes to test my application. I build a test script that runs through a lot of my code; I place it in a file named testall.sql (you'll find it on the companion disk). With trembling fingers, I start my test:

>     SQL> @testall

And, to my severe disappointment, here is what shows up on my screen:

>     ORA-00942: table or view does not exist
>     ORA-00904: invalid column name
>     ORA-00921: unexpected end of SQL command
>     ORA-00936: missing expression

Now, what am I supposed to make of all these error messages? Which error message goes with which SQL statement? Bottom line: when you do lots of dynamic SQL, it is very easy to get very confused and waste lots of time debugging your code -- unless you take precautions as you write your dynamic SQL.

Here are my recommendations:

- Always include an error handling section in code that calls EXECUTE IMMEDIATE and OPEN FOR.

- In each handler, record and/or display the error message and the SQL statement when an error occurs.

How do these recommendations translate into changes in your code? First, let's apply these changes to the execDDL routine, and then generalize from there. Here is the starting point:

>     CREATE OR REPLACE PROCEDURE execDDL (ddl_string IN VARCHAR2)
>        AUTHID CURRENT_USER IS
>     BEGIN
>        EXECUTE IMMEDIATE ddl_string;
>     END;

Now let's add an error handling section to show us problems when they occur:

>     /* Filename on companion disk: execddl.sp */
>     CREATE OR REPLACE PROCEDURE execDDL (ddl_string IN VARCHAR2)
>        AUTHID CURRENT_USER IS
>     BEGIN
>        EXECUTE IMMEDIATE ddl_string;
>     EXCEPTION
>        WHEN OTHERS
>        THEN
>           DBMS_OUTPUT.PUT_LINE (
>              'Dynamic SQL Failure: ' || SQLERRM);
>           DBMS_OUTPUT.PUT_LINE (
>              '   on statement: "' || ddl_string || '"');
>           RAISE;
>     END;

When I use this version to attempt to create a table using really bad syntax, this is what I see:

>     SQL> exec execddl ('create table x')
>     Dynamic SQL Failure: ORA-00906: missing left parenthesis
>        on statement: "create table x"

Of course, in your production version, you might want to consider something a bit more sophisticated than the DBMS_OUTPUT built-in package.

> TIP: With DBMS_SQL, if your parse request fails and you do not explicitly close your cursor in the error section, that cursor remains open (and uncloseable), leading to possible "maximum open cursors exceeded" errors. This will not happen with NDS; cursor variables declared in a local scope are automatically closed -- and memory released -- when the block terminates.

Now let's broaden our view a bit: when you think about it, the execDDL procedure is not really specific to DDL statements. It can be used to execute any SQL string that does not require either USING or INTO clauses. From that perspective, we now have a single program that can and should be used in place of a direct call to EXECUTE IMMEDIATE -- it has all that error handling built in. I supply such a procedure in the ndsutil package (see Section 4.7).

We could even create a similar program for OPEN FOR -- again, only for situations that do not require a USING clause. Since OPEN FOR sets a cursor value, we would probably want to implement it as a function, which would return a type of weak REF CURSOR. This leads right to a packaged implementation along these lines:

>     PACKAGE ndsutil
>     IS
>        TYPE cv_type IS REF CURSOR;

>        FUNCTION openFor (sql_string IN VARCHAR2) RETURN cv_type;
>     END;

The NDS utility package (available on the companion disk in ndsutil.pkg ) contains the complete implementation of this function; the body is quite similar to the execDDL procedure shown earlier.

### 4.6.3 Dynamic PL/SQL

I think I can safely say that some of the most enjoyable moments I have had with PL/SQL (and, believe me, given all the time I spend with the language, I keep a sharp eye out for those moments!) occurred when I was constructing and executing PL/SQL blocks of code dynamically.

Think of it: while a user is running your application, it can do any of the following:

- Create a program, including a package that contains globally accessible data structures

- Obtain (and modify) by name the value of global variables

- Call functions and procedures whose names are not known at compile time

I have used this technique to build very flexible code generators, softcoded calculation engines for users, and much more. Dynamic PL/SQL allows you to work at a higher level of generality, which can be both challenging and exhilarating.

There are some rules and tips you need to keep in mind when working with dynamic PL/SQL blocks and NDS:

- The dynamic string must be a valid PL/SQL block. It must start with the DECLARE or BEGIN keywords, and end with an END statement and a semicolon. The string will not be considered PL/SQL code unless it ends with a semicolon.

- In your dynamic block, you can only access PL/SQL code elements that have global scope (standalone functions and procedures, and elements defined in the specification of a package). Dynamic PL/SQL blocks execute outside the scope of the local enclosing block.

- Errors raised within a dynamic PL/SQL block can be trapped and handled by the local block in which the string was run with the EXECUTE IMMEDIATE statement.

Let's explore these rules so as to avoid any confusion. First of all, I will build a little utility to execute dynamic PL/SQL:

>     /* Filename on companion disk: dynplsql.sp */
>     CREATE OR REPLACE PROCEDURE dynPLSQL (blk IN VARCHAR2)
>     IS
>     BEGIN
>        EXECUTE IMMEDIATE
>           'BEGIN ' || RTRIM (blk, ';') || '; END;';
>     END;
>     /

This one program encapsulates many of the rules mentioned previously for PL/SQL execution. By enclosing the string within a BEGIN-END pairing, I guarantee that whatever I pass in is executed as a valid PL/SQL block. For instance, I can execute the calc_ totals procedure dynamically as simply as this:

>     SQL> exec dynPLSQL ('calc_totals');

Now let's use this program to examine what kind of data structures you can reference within a dynamic PL/SQL block. In the following anonymous block, I want to use DBMS_SQL to assign a value of 5 to the local variable num:

>     <<dynamic>>
>     DECLARE
>        num NUMBER;
>     BEGIN
>        dynPLSQL ('num := 5');
>     END;
>     /

This string is executed within its own BEGIN-END block, which would appear to be a nested block within the anonymous block named "dynamic" with the label. Yet when I execute this script I receive the following error:

>     PLS-00302: component 'NUM' must be declared
>     ORA-06512: at "SYS.DBMS_SYS_SQL", line 239

The PL/SQL engine is unable to resolve the reference to the variable named num. I get the same error even if I qualify the variable name with its block name:

>     <<dynamic>>
>     DECLARE
>        num NUMBER;
>     BEGIN
>        /* Also causes a PLS-00302 error! */
>        dynPLSQL ('dynamic.num := 5');
>     END;
>     /

Now suppose that I define the num variable inside a package called dynamic:

>     CREATE OR REPLACE PACKAGE dynamic
>     IS
>        num NUMBER;
>     END;
>     /

I am now able to execute the dynamic assignment to this newly defined variable successfully:

>     BEGIN
>        dynPLSQL ('dynamic.num := 5');
>     END;
>     /

What's the difference between these two pieces of data? In my first attempt, the variable num is defined locally in the anonymous PL/SQL block. In my second attempt, num is a public global variable defined in the dynamic package. This distinction makes all the difference with dynamic PL/SQL.

It turns out that a dynamically constructed and executed PL/SQL block is not treated as a nested block; instead, it is handled as if it were a procedure or function called from within the current block. So any variables local to the current or enclosing blocks are not recognized in the dynamic PL/SQL block. You can only make references to globally defined programs and data structures. These PL/SQL elements include standalone functions and procedures and any elements defined in the specification of a package.

Fortunately, the dynamic block is executed within the context of the calling block. If you have an exception section within the calling block, it will trap exceptions raised in the dynamic block. So if I execute this anonymous block in SQL*Plus:

>     BEGIN
>        dynPLSQL ('undefined.packagevar := ''abc''');
>     EXCEPTION
>        WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLCODE);
>     END;
>     /

I will not get an unhandled exception.

> TIP: The assignment performed in this anonymous block is an example of indirect referencing. I don't reference the variable directly, but instead do so by specifying the name of the variable. Oracle Developer's FormsBuilder product (formerly known as SQL*Forms and Oracle Forms) offers an implementation of indirect referencing with the NAME_IN and COPY programs. This feature allows developers to build logic that can be shared across all forms in the application. PL/SQL does not support indirect referencing, but you can implement it with dynamic PL/SQL. See the dynvar.pkg file on the disk for an example of such an implementation.

In the following sections, I offer several examples of dynamic PL/SQL to spark your interest and, perhaps, inspire your creativity.

#### 4.6.3.1 Dramatic code reduction

Here is a true story, I kid you not. I once spent some time at an insurance company here in Chicago. Now, top management at insurance companies is notorious for burying any sense of compassion under a mountain of red tape -- and that inclination gets pushed down into the software we have to write. So, for example, a policy might have hundreds of lines of fine print, each of which has a number associated with it, and each of which applies or does not apply to a given claim.

For each line number, the developers had written a "process line" procedure. So if they needed to process line 1, they would call:

>     process_line1

If line 514 applied to the claim, then it was time to call:

>     process_line514

The remarkable thing about this situation is that the developers ended up with a program like this:

>     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;
>        ...
>        END IF;
>     END;

and it was so long that it often would fail to compile, and when it did manage to compile, it took a long time to execute. Nasty!

Dynamic SQL is, of course, suited perfectly to this scenario, and I was able to fix their problem in no time at all (well, to be honest, at the time I fixed it using DBMS_SQL, but 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. I like it!

#### 4.6.3.2 Generic calculator function

Here's the scenario for which I wrote the dyncalc function shown in this section: 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 "soft code" my application, so that (ideally) 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:

>     /* Filename on companion disk: 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 the SQL*Plus session below:

>     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

4.5 Working with Objects and Collections |  | 4.7 NDS Utility Package

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.