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 of 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 all 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. This error usually tells you exactly what is wrong with the SQL string, but that information can still leave much to be desired. 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, and rely on some standard exception-handling programs I have built to 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 O'Reilly site). 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 existORA-00904: invalid column nameORA-00921: unexpected end of SQL commandORA-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.

· You might also want to consider adding a "trace" in front of these statements so that you can easily watch the dynamic SQL as it constructed and executed.

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 ISBEGIN EXECUTE IMMEDIATE ddl_string;END;

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

/* File on web: execddl.sp */CREATE OR REPLACE PROCEDURE execDDL (ddl_string IN VARCHAR2) AUTHID CURRENT_USER ISBEGIN 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.

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 ORA-01000: maximum open cursors exceeded errors. This will not happen with NDS; cursor variables declared in a local scope are automatically closed—and the 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 the later Section 15.6).

We could even create a similar program for OPEN FOR—again, only for situations that do not require a USING clause. Because 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 ndsutilIS TYPE cv_type IS REF CURSOR; FUNCTION openFor (sql_string IN VARCHAR2) RETURN cv_type;END;

The NDS utility package (available from the O'Reilly site in ndsutil.pkg) contains the complete implementation of this function; the body is quite similar to the execDDL procedure shown earlier.

15.5.3 Dynamic PL/SQL

Dynamic PL/SQL offers some of the most interesting and challenging coding opportunities. Think of it: while a user is running your application, you can take advantage of NDS to 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 keyword, and end with an END statement and semicolon. The string will not be considered PL/SQL code unless it ends with a semicolon.

· In your dynamic block, you can access only 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. First, I will build a little utility to execute dynamic PL/SQL:

/* File on web: dynplsql.sp */CREATE OR REPLACE PROCEDURE dynPLSQL (blk IN VARCHAR2)ISBEGIN 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 aBEGIN-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 dynamic 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 appears to be a nested block within the anonymous block named "dynamic". Yet when I execute this script I receive the following error:

PLS-00201: identifier 'NUM' must be declaredORA-06512: at "SCOTT.DYNPLSQL", line 4

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 dynamicIS 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 make references only 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.

The assignment performed in this anonymous block is an example ofindirect referencing. I don't reference the variable directly, but instead do so by specifying the name of the variable. Oracle Forms Builder 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 O'Reilly site for an example of such an implementation.

The following sections offer a few examples of dynamic PL/SQL to spark your interest and, perhaps, inspire your creativity.