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.