Oracle PL/SQL Programming Guide to Oracle8i Features By Steven Feuerstein 1st Edition October 1999 1-56592-675-7, Order Number: 6757 272 pages, $29.95 ,Includes Diskette

Chapter 4 Native Dynamic SQL in Oracle8i

In this chapter:
DBMS_SQL Versus NDS
NDS Statement Summary
Multirow Queries with Cursor Variables
Binding Variables
Working with Objects and Collections
Building Applications with NDS
NDS Utility Package

Ever since Oracle 7.1, we PL/SQL developers have been able to use the built-in DBMS_SQL package to execute dynamic SQL and PL/SQL. This means, for example, that at runtime you can construct a query, a DELETE statement, a CREATE TABLE statement, or even a PL/SQL block as a string--and then execute it. Dynamic SQL comes in extremely handy when you are building ad hoc query systems, when you need to execute DDL inside PL/SQL, and just generally when you don't know in advance exactly what you need to do or what the user will want to do. Dynamic SQL is a frequent requirement in Web-based applications.

But there are some problems with DBMS_SQL:

• It is a very complicated package.
• It has a number of restrictions (such as not recognizing and being able to work with new Oracle8 datatypes).
• It is relatively slow.

So our dear friends at PL/SQL Central in Redwood Shores took pity on us all and reimplemented dynamic SQL directly in the PL/SQL language itself. This new facility is called native dynamic SQL. I will refer to it as NDS in this chapter.

Here's the free advertisement for Oracle Corporation: NDS is faster and easier than DBMS_SQL. Truth in advertising? Absolutely, although my tests indicate that with the performance enhancements already in place for DBMS_SQL, NDS is on average just slightly faster. There is no doubt, however, that NDS is much easier to use--when you can use it.

Before diving into the syntax and details of NDS, let's take a look at a comparison between the two approaches to dynamic SQL.

DBMS_SQL Versus NDS

Let's compare the DBMS_SQL and NDS implementations of a program that displays all the employees for the specified and very dynamic WHERE clause.

The DBMS_SQL implementation:

CREATE OR REPLACE PROCEDURE showemps (
   where_in IN VARCHAR2 := NULL)
IS
   cur INTEGER := DBMS_SQL.OPEN_CURSOR;
   rec employee%ROWTYPE;
   fdbk INTEGER;
BEGIN
   DBMS_SQL.PARSE
     (cur, 
      'SELECT employee_id, last_name 
         FROM employee 
        WHERE ' || NVL (where_in, '1=1'),
      DBMS_SQL.NATIVE);
 
   DBMS_SQL.DEFINE_COLUMN (cur, 1, 1);
   DBMS_SQL.DEFINE_COLUMN (cur, 2, user, 30);
 
   fdbk := DBMS_SQL.EXECUTE (cur);
   LOOP
      /* Fetch next row. Exit when done. */
      EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0;
      DBMS_SQL.COLUMN_VALUE (cur, 1, rec.employee_id);
      DBMS_SQL.COLUMN_VALUE (cur, 2, rec.last_name);
      DBMS_OUTPUT.PUT_LINE (
         TO_CHAR (rec.employee_id) || '=' || 
         rec.last_name);
   END LOOP;
 
   DBMS_SQL.CLOSE_CURSOR (cur);
END;
/

The NDS implementation:

CREATE OR REPLACE PROCEDURE showemps (
   where_in IN VARCHAR2 := NULL)
IS
   TYPE cv_typ IS REF CURSOR;
   cv cv_typ;
   v_id employee.employee_id%TYPE;
   v_nm employee.last_name%TYPE;
BEGIN
   OPEN cv FOR 
      'SELECT employee_id, last_name 
         FROM employee 
        WHERE ' || NVL (where_in, '1=1');
   LOOP
      FETCH cv INTO v_id, v_nm;
      EXIT WHEN cv%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (
         TO_CHAR (v_id) || '=' || v_nm);
   END LOOP;
   CLOSE cv;
END;
/

As you can see (and this is true in general), you can write dramatically less code using NDS. And since the code you write relies less on built-in packaged programs and more on native, standard elements of PL/SQL, that code is easier to build, read, and maintain.

Given this situation, why would anyone use DBMS_SQL ever again? Because NDS cannot do everything and anything you might want to do. The following lists show the operations that can be performed exclusively by each of these dynamic SQL implementations.

Exclusive NDS capabilities:

• Works with all SQL datatypes, including user-defined objects and collection types (variable arrays, nested tables, and index-by tables). DBMS_SQL only works with Oracle7-compatible datatypes.
• Allows you to fetch multiple columns of information directly into a PL/SQL record. With DBMS_SQL, you must fetch into individual variables.

Exclusive DBMS_SQL capabilities:

• Supports Method 4 dynamic SQL, which means that at compile time, you don't know how many columns you will be querying and/or how many bind variables will need to be set. Method 4 is the most complex form of dynamic SQL, and NDS doesn't support it (except under certain restricted circumstances).
• As of Oracle8, allows you to describe the columns of your dynamic cursor, obtaining column information in an index-by table of records.
• Supports SQL statements that are more than 32KB in length.
• Supports the use of the RETURNING clause into an array of values; NDS only allows the use of RETURNING for a single statement.
• Allows you to reuse your dynamic SQL cursors, which can improve performance.
• Can be executed from client-side (Oracle Developer) applications.

For more information about DBMS_SQL and the listed capabilities of this code, please see Chapter 3 of Oracle Built-in Packages (O'Reilly & Associates, 1998).

What can we conclude from these lists? The NDS implementation will be able to handle something like 80 to 90% of the dynamic SQL requirements you are likely to face. It is good to know, however, that there is still a place for DBMS_SQL (especially since I wrote a 100-page chapter on that package in Oracle Built-in Packages).

NDS Statement Summary

One of the nicest things about NDS is its simplicity. Unlike DBMS_SQL, which has dozens of programs and lots of rules to follow, NDS has been integrated into the PL/SQL language by adding one new statement, EXECUTE IMMEDIATE, and by enhancing the existing OPEN FOR statement:

EXECUTE IMMEDIATE

Executes a specified SQL statement immediately

OPEN FOR

Allows you to perform multiple-row dynamic queries

The EXECUTE IMMEDIATE Statement

Use EXECUTE IMMEDIATE to execute (immediately!) the specified SQL statement. Here is the syntax of this statement:

EXECUTE IMMEDIATE SQL_string
   [INTO {define_variable[, define_variable]... | record}]
   [USING [IN | OUT | IN OUT] bind_argument
       [, [IN | OUT | IN OUT] bind_argument]...];
 

SQL_string

A string expression containing the SQL statement or PL/SQL block

define_variable

A variable that receives a column value returned by a query

record

A record based on a user-defined TYPE or %ROWTYPE that receives an entire row returned by a query

bind_argument

An expression whose value is passed to the SQL statement or PL/SQL block

INTO clause

Use for single-row queries; for each column value returned by the query, you must supply an individual variable or field in a record of compatible type.

USING clause

Allows you to supply bind arguments for the SQL string. This clause is used for both dynamic SQL and PL/SQL, which is why you can specify a parameter mode. This usage is only relevant for PL/SQL, however; the default is IN, which is the only kind of bind argument you would have for SQL statements.

You can use EXECUTE IMMEDIATE for any SQL statement or PL/SQL block, except for multiple-row queries. If SQL_string ends with a semicolon, it will be treated as a PL/SQL block; otherwise, it will be treated as either DML (Data Manipulation Language--SELECT, INSERT, UPDATE, or DELETE) or DDL (Data Definition Language, such as CREATE TABLE). The string may contain placeholders for bind arguments, but you cannot use bind values to pass in the names of schema objects, such as table names or column names.

When the statement is executed, the runtime engine replaces each placeholder (an identifier with a colon in front of it, such as :salary_value) in the SQL string with its corresponding bind argument (by position). You can pass numeric, date, and string expressions. You cannot, however, pass a Boolean, because it is a PL/SQL datatype. Nor can you pass a NULL literal value. Instead, you must pass a variable of the correct type that has a value of NULL.

NDS supports all SQL datatypes available in Oracle8i. So, for example, define variables and bind arguments can be collections, large objects (LOBs), instances of an object type, and REFs. On the other hand, NDS does not support datatypes that are specific to PL/SQL, such as Booleans, index-by tables, and user-defined record types. The INTO clause may, however, contain a PL/SQL record.

Let's take a look at a few examples:

1. Create an index:

EXECUTE IMMEDIATE 'CREATE INDEX emp_u_1 ON employee (last_name)';

It can't get much easier than that, can it?

2. Create a stored procedure that will execute any DDL statement:

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

With execDDL in place, I can create that same index as follows:

execDDL ('CREATE INDEX emp_u_1 ON employee (last_name)');

3. Obtain the count of rows in any table, in any schema, for the specified WHERE clause:

/* Filename on companion disk: tabcount.sf */
CREATE OR REPLACE FUNCTION tabCount (
   tab IN VARCHAR2,
   whr IN VARCHAR2 := NULL,
   sch IN VARCHAR2 := NULL)
   RETURN INTEGER
IS
   retval INTEGER;
BEGIN
   EXECUTE IMMEDIATE
      'SELECT COUNT(*) 
         FROM ' || NVL (sch, USER) || '.' || tab ||
      ' WHERE ' || NVL (whr, '1=1')
      INTO retval;
   RETURN retval;
END;
/

So now I never again have to write SELECT COUNT(*), whether in SQL*Plus or within a PL/SQL program, as in the following:

BEGIN
   IF tabCount ('emp', 'deptno = ' || v_dept) > 100
   THEN
      DBMS_OUTPUT.PUT_LINE ('Growing fast!');
   END IF;

4. Here's a function that lets you update the value of any numeric column in any table. It's a function because it returns the number of rows that have been updated.

/* Filename on companion disk: updnval.sf */

 

CREATE OR REPLACE FUNCTION updNVal (
   tab IN VARCHAR2,
   col IN VARCHAR2,
   val IN NUMBER,
   whr IN VARCHAR2 := NULL,
   sch IN VARCHAR2 := NULL)
   RETURN INTEGER
IS
BEGIN
   EXECUTE IMMEDIATE
      'UPDATE ' || NVL (sch, USER) || '.' || tab ||
      '   SET ' || col || ' = :the_value 
        WHERE ' || NVL (whr, '1=1')
     USING val;
   RETURN SQL%ROWCOUNT;
END;
/

Where I come from, that is a very small amount of code to achieve all of that flexibility! This example introduces the bind argument: after the UPDATE statement is parsed, the PL/SQL engine replaces the :the_value placeholder with the value in the val variable. Notice also that I am able to rely on the SQL%ROWCOUNT cursor attribute that I have already been using for static DML statements.

5. Suppose that I need to run a different stored procedure at 9 a.m. each day of the week. Each program's name has this structure: DAYNAME_set_schedule. Each procedure has the same four arguments: you pass in employee_id and hour for the first meeting of the day; it returns the name of the employee and the number of appointments for the day. I can use dynamic PL/SQL to handle this situation:

/* Filename on companion disk: run9am.sp */

 

CREATE OR REPLACE PROCEDURE run_9am_procedure (
   id_in IN employee.employee_id%TYPE,
   hour_in IN INTEGER)
IS
   v_apptCount INTEGER;
   v_name VARCHAR2(100);
BEGIN
   EXECUTE IMMEDIATE
      'BEGIN ' || TO_CHAR (SYSDATE, 'DAY') || 
         '_set_schedule (:id, :hour, :name, :appts); END;'
     USING IN 
        id_in, IN hour_in, OUT v_name, OUT v_apptCount;
 
   DBMS_OUTPUT.PUT_LINE (
      'Employee ' || v_name || ' has ' || v_apptCount ||
      ' appointments on ' || TO_CHAR (SYSDATE));
END;
/

This is a very easy and accessible syntax!

The OPEN FOR Statement

The OPEN FOR statement is not brand-new to PL/SQL in Oracle8i; it was first offered in Oracle7 to support cursor variables. Now it is deployed in an especially elegant fashion to implement multiple-row dynamic queries. With DBMS_SQL, you go through a particularly painful series of steps to implement multirow queries: parse, bind, define each column individually, execute, fetch, extract each column value individually. My gosh, what a lot of code to write!

For native dynamic SQL, Oracle took an existing feature and syntax--that of cursor variables--and extended it in a very natural way to support dynamic SQL. The next section explores multirow queries in detail; let's take a look now specifically at the syntax of the OPEN FOR statement:

OPEN {cursor_variable | :host_cursor_variable} FOR SQL_string
   [USING bind_argument[, bind_argument]...];
 

cursor_variable

A weakly typed cursor variable

:host_cursor_variable

A cursor variable declared in a PL/SQL host environment such as an Oracle Call Interface (OCI) program

SQL_string

Contains the SELECT statement to be executed dynamically

USING clause

Follows the same rules as it does in the EXECUTE IMMEDIATE statement

Many PL/SQL developers are not very familiar with cursor variables, so a quick review is in order (for lots more details, check out Chapter 6 of Oracle PL/SQL Programming, second edition).

A cursor variable is a variable of type REF CURSOR, or referenced cursor. Here is an example of a declaration of a cursor variable based on a "weak" REF CURSOR (the sort you will use for NDS):

DECLARE
   TYPE cv_type IS REF CURSOR;
   cv cv_type;

A cursor variable points to a cursor object; it is, however, a variable. You can have more than one variable pointing to the same cursor object, you can assign one cursor variable to another, and so on. Once you have declared a cursor variable, you can assign a value to it by referencing it in an OPEN FOR statement:

DECLARE
   TYPE cv_type IS REF CURSOR;
   cv cv_type;
BEGIN
   OPEN cv FOR SELECT COUNT(guns) FROM charlton_heston_home;

In this example, the query is static--it is not contained in single quotes, and it is frozen at compilation time. That is the only way we have been able to work with cursor variables until Oracle8i. Now we can use the same syntax as before, but the query can be a literal or an expression, as in the following:

OPEN dyncur FOR SQL_string;

or, to show the use of a bind argument:

OPEN dyncur FOR 
   'SELECT none_of_the_above FROM senate_candidates
     WHERE state = :your_state_here'
   USING state_in;

Once you have opened the query with the OPEN FOR statement, the syntax used to fetch rows, close the cursor variable and check the attributes of the cursor are all the same as for static cursor variables--and hardcoded explicit cursors, for that matter. The next section demonstrates all of this syntax through examples.

To summarize, there are two differences between the OPEN FOR statement for static and dynamic SQL:

• The static version does not support the USING clause of NDS.
• The static version requires a static SQL statement after the FOR keyword, whereas with NDS, the SQL string is always either a literal string or an expression that evaluates to a string.

Multirow Queries with Cursor Variables

Now that you have seen the syntax of OPEN FOR and been introduced to cursor variables, let's explore the nuances involved in multirow queries with NDS.

When you execute an OPEN FOR statement, the PL/SQL runtime engine does the following:

1. Associates a cursor variable with the query found in the query string
2. Evaluates any bind arguments and substitutes those values for the placeholders found in the query string
3. Executes the query
4. Identifies the result set
5. Positions the cursor on the first row in the result set
6. Zeros out the rows-processed count returned by %ROWCOUNT

Note that any bind arguments (provided in the USING clause) in the query are evaluated only when the cursor variable is opened. This means that if you want to use a different set of bind arguments for the same dynamic query, you must issue a new OPEN FOR statement with those arguments.

TIP: This approach is actually less efficient than the DBMS_SQL approach, which will allow you to simply rebind and then execute without having to reparse.

To perform a multirow query, you take these steps:

1. Declare a REF CURSOR type (if one is not already available, as it could be if defined in a package specification).
2. Declare a cursor variable based on the REF CURSOR.
3. OPEN the cursor variable FOR your query string.
4. Use the FETCH statement to fetch one row at a time from the query.
5. Check cursor attributes (%FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN) as necessary.
6. Close the cursor variable using the normal CLOSE statement.

Here is a simple program to display the specified column of any table for the rows indicated by the WHERE clause (it will work for number, date, and string columns):

/* Filename on companion disk: showcol.sp */

 

CREATE OR REPLACE PROCEDURE showcol (
   tab IN VARCHAR2, 
   col IN VARCHAR2, 
   whr IN VARCHAR2 := NULL)
IS
   TYPE cv_type IS REF CURSOR;
   cv cv_type; 
   val VARCHAR2(32767);  
BEGIN
   /* Construct the very dynamic query and open the cursor. */
   OPEN cv FOR 
      'SELECT ' || col || 
      '  FROM ' || tab ||
      ' WHERE ' || NVL (whr, '1 = 1');
      
   LOOP
      /* Fetch the next row, and stop if no more rows. */
      FETCH cv INTO val;
      EXIT WHEN cv%NOTFOUND;
 
      /* Display the data, with a header before the first row. */
      IF cv%ROWCOUNT = 1
      THEN
         DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-'));
         DBMS_OUTPUT.PUT_LINE (
            'Contents of ' || 
            UPPER (tab) || '.' || UPPER (col));
         DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-'));
      END IF;
      DBMS_OUTPUT.PUT_LINE (val);
   END LOOP;
   
   CLOSE cv; --All done, so clean up!
END;
/   

Here are some examples of output from this procedure:

SQL> exec showcol ('emp', 'ename', 'deptno=10')
--------------------------------------------------
Contents of EMP.ENAME
--------------------------------------------------
CLARK
KING
MILLER

I can even combine columns:

BEGIN
   showcol (
      'emp', 
      'ename || ''-$'' || sal', 
      'comm IS NOT NULL');
END;
/
--------------------------------------------------
Contents of EMP.ENAME || '-$' || SAL
--------------------------------------------------
ALLEN-$1600
WARD-$1250
MARTIN-$1250
TURNER-$1500

FETCH into Variables or Records

The FETCH statement in the showcol procedure shown in the previous section fetches into an individual variable. You could also FETCH into a sequence of variables, as shown here:

DECLARE
   TYPE cv_type IS REF CURSOR;
   cv cv_type; 
   mega_bucks company.ceo_compensation%TYPE;
   achieved_by company.layoff_count%TYPE;  
BEGIN
   OPEN cv FOR 
      'SELECT ceo_compensation, layoff_count
        FROM company
       WHERE ' || NVL (whr, '1 = 1');
      
   LOOP
      FETCH cv INTO mega_bucks, achieved_by;

Working with a long list of variables in the FETCH list gets cumbersome and inflexible: you have to declare the variables, keep that set of values synchronized with the FETCH statement, and so on. To ease our troubles, NDS allows us to fetch into a record, as shown here:

DECLARE
   TYPE cv_type IS REF CURSOR;
   cv cv_type; 
   ceo_info company%ROWTYPE;
BEGIN
   OPEN cv FOR 
      'SELECT *
        FROM company
       WHERE ' || NVL (whr, '1 = 1');
      
   LOOP
      FETCH cv INTO ceo_info;

Of course, in many situations you will not want to do a SELECT *; this statement can be very inefficient if your table has hundreds of columns and you only need to work with three of those hundreds. A better approach is to create record TYPEs that correspond to different requirements. The best place to put these structures is in a package specification, so they can be used throughout your application. Here's one such package:

CREATE OR REPLACE PACKAGE company_struc
IS
   TYPE dynsql_curtype IS REF CURSOR;
 
   TYPE ceo_info_rt IS RECORD (
      mega_bucks company.ceo_compensation%TYPE,
      achieved_by company.layoff_count%TYPE);
 
END company_struc;

With this package in place, I can rewrite my CEO-related code as follows:

DECLARE
   cur company_struc.dynsql_curtype; 
   rec company_struc.ceo_info_rt; 
BEGIN
   OPEN cv FOR 
      'SELECT ceo_compensation, layoff_count
        FROM company
       WHERE ' || NVL (whr, '1 = 1');
      
   LOOP
      FETCH cv INTO rec;

The USING Clause in OPEN FOR

As with the EXECUTE IMMEDIATE statement, you can pass in bind arguments when you open a cursor. You can only provide IN arguments for a query. By using bind arguments you can improve the performance of your SQL also, and also make it easier to write and maintain that code. (See the "Binding Variables" section later in this chapter for information about this technique.)

Let's revisit the showcol procedure. That procedure accepted a completely generic WHERE clause. Suppose that I have a more specialized requirement: I want to display (or in some other way process) all column information for rows that contain a date column with a value within a certain range. In other words, I want to be able to satisfy this query:

SELECT ename
  FROM emp
 WHERE hiredate BETWEEN x AND y;

as well as this query:

SELECT name
  FROM war_criminal
 WHERE killing_date BETWEEN x AND y;

I also want to make sure that the time component of the date column does not play a role in the WHERE condition.

Here is the header for the procedure:

/* Filename on companion disk: showcol2.sp */

 

PROCEDURE showcol (
   tab IN VARCHAR2,
   col IN VARCHAR2,
   dtcol IN VARCHAR2,
   dt1 IN DATE,
   dt2 IN DATE := NULL)

The OPEN FOR statement now contains two placeholders and a USING clause to match:

OPEN cv FOR 
      'SELECT ' || col || 
      '  FROM ' || tab ||
      ' WHERE ' || dtcol || 
         ' BETWEEN TRUNC (:startdt) 
               AND TRUNC (:enddt)'
   USING dt1, NVL (dt2, dt1+1);

I have crafted this statement so that if the user does not supply an end date, the WHERE clause returns rows whose date column is the same day as the dt1 provided. The rest of the showcol procedure remains the same, except for some cosmetic changes in the display of the header.

The following call to this new version of showcol asks to see the names of all employees hired in 1982:

BEGIN
   showcol ('emp', 
      'ename', 'hiredate', 
      '01-jan-82', '31-dec-82');
END;
/
----------------------------------------------------------------------
Contents of EMP.ENAME for HIREDATE between 01-JAN-82 and 31-DEC-82
----------------------------------------------------------------------
MILLER

Generic GROUP BY Procedure

How many times have you written a query along these lines:

SELECT some-columns, COUNT(*)
  FROM your-table
 GROUP BY some-columns;

And then there is the variation involving the HAVING clause (you don't want to see all the counts, you just want to see those groupings where there is more than one identical value, and so on). These are very common requirements, but with NDS, you can easily build a program that does all the work for you, for any table, and for any single column (and this is extensible to multiple columns as well).

Here is the header of such a procedure:

/* Filename on companion disk: countby.sp */

 

PROCEDURE countBy (
   tab IN VARCHAR2,
   col IN VARCHAR2, 
   atleast IN INTEGER := NULL,
   sch IN VARCHAR2 := NULL,
   maxlen IN INTEGER := 30)
 

tab

The name of the table.

col

The name of the column.

sch

The name of the schema (default of NULL = USER).

atleast

If you supply a non-NULL value for atleast, then the SELECT statement includes a HAVING COUNT(*) greater than that value.

maxlen

Used for formatting of the output.

You can look at the countby.sp file on the companion disk to see the full implementation; here is all the code except that used to do the formatting (header string and so on):

IS
   TYPE cv_type IS REF CURSOR;
   cv cv_type;
   
   SQL_string VARCHAR2(32767) := 
      'SELECT ' || col || ', COUNT(*) 
         FROM ' || NVL (sch, USER) || '.' || tab ||
      ' GROUP BY ' || col;
   
   v_val VARCHAR2(32767);
   v_count INTEGER;
BEGIN
   IF atleast IS NOT NULL
   THEN
      SQL_string := SQL_string || ' HAVING COUNT(*) >= ' || atleast;
   END IF;
  
   OPEN cv FOR SQL_String;
   
   LOOP
      FETCH cv INTO v_val, v_count;
      EXIT WHEN cv%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (RPAD (v_val, maxlen) || ' ' || v_count);
   END LOOP;
   
   CLOSE cv;
END;
/   

As you start to build more and more of these generic utilities, you will find that it doesn't take very much code or effort--you just have to think through the steps of the SQL string construction carefully.

Generic GROUP BY Package

Displaying information is useful for test purposes, but in many cases you want to work with the queried information further, not simply show it. Let's build on the countby procedure shown in the previous section to provide an implementation in which the results of the dynamic query are stored in an index-by table for subsequent analysis.

Here is the specification of the package:

/* Filename on companion disk: countby.pkg */

 

CREATE OR REPLACE PACKAGE grp
IS
   TYPE results_rt IS RECORD (
      val VARCHAR2(4000),
      countby INTEGER);
      
   TYPE results_tt IS TABLE OF results_rt 
      INDEX BY BINARY_INTEGER;
 
   FUNCTION countBy (
      tab IN VARCHAR2,
      col IN VARCHAR2, 
      atleast IN INTEGER := NULL,
      sch IN VARCHAR2 := NULL,
      maxlen IN INTEGER := 30)
      RETURN results_tt;
END grp;
/      

The implementation of the countby function is virtually the same as the procedure. The main difference is that I now have a record structure to fetch into, and an index-by table to fill. You can see both these changes in the loop that fetches the rows:

LOOP
   FETCH cv INTO rec;
   EXIT WHEN cv%NOTFOUND;
   retval(cv%ROWCOUNT) := rec;
END LOOP;

With this package in place, I can very easily build programs that access this analytical information. Here is one example:

/* Filename on companion disk: countby.tst */

 

DECLARE
   results grp.results_tt;
   indx PLS_INTEGER;
   minrow PLS_INTEGER;
   maxrow PLS_INTEGER;
BEGIN
   results := grp.countby ('employee', 'department_id');
   
   /* Find min and max counts. */
   indx := results.FIRST;
   LOOP
      EXIT WHEN indx IS NULL;
 
      IF minrow IS NULL OR
         minrow > results(indx).countby
      THEN 
         minrow := indx; 
      END IF;
      
      IF maxrow IS NULL OR
         maxrow < results(indx).countby
      THEN   
         maxrow := indx; 
      END IF;
 
      /* Perform other processing as well... */
 
      /* Move to next group count. */
      indx := results.NEXT(indx);
   END LOOP;
END;
/

Binding Variables

You have seen several examples of the use of bind variables or arguments with NDS. Let's now go over the various rules and special situations you may encounter when binding.

Binding Versus Concatenation

In most situations, you will be able to take two different paths to insert program values into your SQL string: binding and concatenation. The following table contrasts these approaches for a dynamic UPDATE statement.

Concatenation	Binding
EXECUTE IMMEDIATE 'UPDATE ' || tab 'SET sal = ' || v_sal;	EXECUTE IMMEDIATE 'UPDATE ' || tab 'SET sal = :new_sal' USING v_sal;

Binding involves the use of placeholders and the USING clause; concatenation shortcuts that process by adding the values directly to the SQL string. Two different approaches--which should you use and when?

I recommend that you bind arguments whenever possible (see the next section for limitations on binding) rather than rely on concatenation. There are two reasons for taking this approach:

Binding is faster

When you bind in a value, the SQL string itself does not contain the value, just the placeholder name. Therefore, you can bind different values to the same SQL statement without changing that statement. Since it is the same SQL statement, your application is more likely to be able to take advantage of the pre-parsed cursors that are cached in the System Global Area (SGA) of the database.

Binding is easier to write and maintain

When you bind, you don't have to worry about datatype conversion. It is all handled for you by the NDS engine. Binding, in fact, minimizes datatype conversion, since it works with the native datatypes. If you use concatenation, you will often need to write very complex, error-prone string expressions involving multiple single quotes, TO_DATE and TO_CHAR function calls, and so on. For example, consider the following comparison of concatenation and binding for a more complex statement:

/* Binding */
EXECUTE IMMEDIATE
   'UPDATE employee SET salary = :val 
     WHERE hire_date BETWEEN :lodate AND :hidate'
   USING v_start, v_end;
 
/* Concatenation */
EXECUTE IMMEDIATE
 'UPDATE employee SET salary = ' || val_in ||
 ' WHERE hire_date BETWEEN ' ||
    ' TO_DATE (''' || TO_CHAR (v_start)  || ''')' ||
    ' AND ' ||
    ' TO_DATE (''' || TO_CHAR (v_end)  || ''')';

So bind whenever possible . . . which leads to the question: when is binding not an option?

Limitations on Binding

You can only bind into your SQL statement expressions (literals, variables, complex expressions) that replace placeholders for data values inside the dynamic string. You cannot bind in the names of schema elements (tables, columns, etc.) or entire chunks of the SQL statement (such as the WHERE clause). For those parts of your string, you must use concatenation.

For example, suppose you want to create a procedure that will truncate the specified view or table. Your first attempt might look something like this:

CREATE OR REPLACE PROCEDURE truncobj (
   nm IN VARCHAR2,
   tp IN VARCHAR2 := 'TABLE',
   sch IN VARCHAR2 := NULL)
IS
BEGIN
   EXECUTE IMMEDIATE 
      'TRUNCATE :trunc_type :obj_name'
      USING tp, NVL (sch, USER) || '.' || nm;
END;
/

This code seems perfectly reasonable. But when you try to run the procedure you'll get this error:

ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword

And if you rewrite the procedure to simply truncate tables, as follows:

EXECUTE IMMEDIATE 'TRUNCATE TABLE :obj_name' USING nm;

Then the error becomes:

ORA-00903: invalid table name

Why does NDS (and DBMS_SQL) have this restriction? When you pass a string to EXECUTE IMMEDIATE, the runtime engine must first parse the statement. The parse phase guarantees that the SQL statement is properly defined. PL/SQL can tell that the following statement is valid:

'UPDATE emp SET sal = :xyz'

without having to know the value of :xyz. But how can PL/SQL know if the following statement is well formed?

'UPDATE emp SET :col_name = :xyz'

Even if you don't pass in nonsense for col_name, it won't work. For that reason, you must use concatenation:

CREATE OR REPLACE PROCEDURE truncobj (
   nm IN VARCHAR2,
   tp IN VARCHAR2 := 'TABLE',
   sch IN VARCHAR2 := NULL)
IS
BEGIN
   EXECUTE IMMEDIATE 
      'TRUNCATE ' || tp || ' ' || NVL (sch, USER) || '.' || nm;
END;
/

Argument Modes

Bind arguments can have one of three modes:

IN

Read-only value (the default mode)

OUT

Write-only variable

IN OUT

Can read the value coming in and write the value going out

When you are executing a dynamic query, all bind arguments must be IN mode, except when you are taking advantage of the RETURNING clause, as shown here:

CREATE OR REPLACE PROCEDURE wrong_incentive (
   company_in IN INTEGER,
   new_layoffs IN NUMBER
   )
IS
   sql_string VARCHAR2(2000);
   sal_after_layoffs NUMBER;
BEGIN
   sql_string := 
      'UPDATE ceo_compensation
          SET salary = salary + 10 * :layoffs
        WHERE company_id = :company
       RETURNING salary INTO :newsal';
      
   EXECUTE IMMEDIATE sql_string 
     USING new_layoffs, company_in, OUT sal_after_layoffs;
   
   DBMS_OUTPUT.PUT_LINE (
      'Benefiting from the misery of others at $' || sal_after_layoffs);
END;

Besides being used with the RETURNING clause, OUT and IN OUT bind arguments come into play mostly when you are executing dynamic PL/SQL. In this case, the modes of the bind arguments must match the modes of any PL/SQL program parameters, as well as the usage of variables in the dynamic PL/SQL block.

Let's take a look at how this works with a few examples. Suppose that I have created the following stored procedure (I am writing this text in May 1999, as Kosovar Albanians are being pushed from their homes by Milosevic, and NATO bombs ravage Yugoslavia):

/* Filename on companion disk: natotarg.sql */

 

PROCEDURE pick_nato_targets (
   media_outlet_ok IN BOOLEAN,
   electric_grid_ok IN BOOLEAN,
   maternity_ward_ok IN BOOLEAN,
   cumulative_regrets IN OUT NUMBER,
   civilian_casualities OUT NUMBER
   )

Now I will just wander kind of naively into the territory of dynamic PL/SQL and execute the procedure, as follows:

BEGIN
   EXECUTE IMMEDIATE
      'BEGIN
          pick_nato_targets (TRUE, TRUE, TRUE, 10, 100);
       END;';
END;
/

Since cumulative_regrets is an IN OUT argument, however, I get these errors:

PLS-00363: expression '10' cannot be used as an assignment target
PLS-00363: expression '100' cannot be used as an assignment target

The procedure wants to pass back a value through the last two arguments. I need to provide a data structure to hold those values. Literals will not do, so I change it to this:

DECLARE 
   next_to_old_defense_building BOOLEAN := TRUE;
   we_all_make_mistakes NUMBER;
   others_die_for_them NUMBER;
BEGIN
   EXECUTE IMMEDIATE
      'BEGIN
          pick_nato_targets (
             TRUE, TRUE, :baby_place, :whoops, :it_happens);
       END;'
       USING next_to_old_defense_building, 
             we_all_make_mistakes, 
             others_die_for_them;
END;
/

And now I get the following error:

PLS-00457: in USING clause, expressions have to be of SQL types

which reminds me that even though I am running dynamic PL/SQL, I have to conform to the rules and restrictions of NDS: only SQL datatypes are allowed, and Boolean is still not one of them, though I sure don't understand why.

But, fine, I will not pass in the Boolean value; I'll stick to numeric bind values:

   EXECUTE IMMEDIATE
      'BEGIN
          pick_nato_targets (
             TRUE, TRUE, TRUE, :whoops, :it_happens);
       END;'
       USING we_all_make_mistakes, 
             others_die_for_them;

But then I get this error:

ORA-06536: IN bind variable bound to an OUT position

I have left both bind arguments with the default IN mode, and that does not match the arguments. And if I change them both to OUT:

USING OUT we_all_make_mistakes, 
      OUT others_die_for_them;

I get this error:

ORA-06537: OUT bind variable bound to an IN position

That would seem to be darn confusing, but the reality is that when you have an IN OUT argument, the error message treats it as if it's an IN argument.

And so we find that the only way to call this procedure successfully in NDS is with the following statement:

EXECUTE IMMEDIATE
   'BEGIN
       pick_nato_targets (
          TRUE, TRUE, FALSE, :whoops, :it_happens);
    END;'
    USING IN OUT we_all_make_mistakes, 
          OUT others_die_for_them;

Duplicate Placeholders

In a dynamically constructed and executed SQL string, NDS associates placeholders with USING clause bind arguments by position, rather than by name. The treatment of multiple placeholders with the same name varies, however, according to whether you are using dynamic SQL or dynamic PL/SQL. You need to follow these rules:

• When you are executing a dynamic SQL string (DML or DDL; in other words, the string does not end in a semicolon), you must supply an argument for each placeholder, even if there are duplicates.
• When you are executing a dynamic PL/SQL block (the string ends in a semicolon), you supply an argument for each unique placeholder.

Passing NULL Values

We will all encounter special moments when we want to pass a NULL value as a bind argument, as follows:

EXECUTE IMMEDIATE 
   'UPDATE employee SET salary = :newsal
     WHERE hire_date IS NULL' 
   USING NULL;

You will, however, get this error:

PLS-00457: in USING clause, expressions have to be of SQL types

Basically, what this is saying is that NULL has no datatype, and "no datatype" is not a valid SQL datatype.

So what are you supposed to do if you need to pass in a NULL value? You can do one of two things:

1. Hide it behind a variable façade, most easily done with an uninitialized variable, as shown here:

DECLARE
   /* Default initial value is NULL */
   no_salary_when_fired NUMBER;
BEGIN
    EXECUTE IMMEDIATE 
      'UPDATE employee SET salary = :newsal
        WHERE hire_date IS NULL' 
      USING no_salary_when_fired;
END;
Use a conversion function to convert the NULL value to a typed value explicitly:
BEGIN
    EXECUTE IMMEDIATE 
      'UPDATE employee SET salary = :newsal
        WHERE hire_date IS NULL' 
      USING TO_NUMBER (NULL);
END;

Working with Objects and Collections

One of the most important advantages of NDS over DBMS_SQL is its support for new Oracle8 datatypes: objects and collections. You don't need to change the structure of the code you write in NDS to use it with objects and collections.

Suppose that I am building an internal administrative system for the national health management corporation Health$.Com. To reduce costs, the system will work in a distributed manner, creating and maintaining separate tables of customer information for each for-profit hospital owned by Health$.Com.

I'll start by defining an object type (person) and VARRAY type (preexisting_conditions), as follows:

CREATE TYPE person AS OBJECT (
   name VARCHAR2(50), dob DATE, income NUMBER);
/
 
CREATE TYPE preexisting_conditions IS TABLE OF VARCHAR2(25);
/

Once these types are defined, I can build a package to manage my most critical health-related information--data needed to maximize profits at Health$.Com. Here is the specification:

/* Filename on companion disk: health$.pkg */

 

CREATE OR REPLACE PACKAGE health$
AS
   PROCEDURE setup_new_hospital (hosp_name IN VARCHAR2);
 
   PROCEDURE add_profit_source (
      hosp_name IN VARCHAR2, 
      pers IN Person, 
      cond IN preexisting_conditions);
 
   PROCEDURE weed_out_poor_and_sick (
      hosp_name VARCHAR2,
      min_income IN NUMBER := 100000,
      max_preexist_cond IN INTEGER := 0);
 
   PROCEDURE show_profit_centers (hosp_name VARCHAR2);
 END health$;
/

With this package, I can do the following:

1. Set up a new hospital, which means create a new table to hold information about that hospital. Here's the implementation from the body:

FUNCTION tabname (hosp_name IN VARCHAR2) IS
BEGIN
   RETURN hosp_name || '_profit_center';
END;
 
PROCEDURE setup_new_hospital (hosp_name IN VARCHAR2) IS
BEGIN
   EXECUTE IMMEDIATE 
      'CREATE TABLE ' || tabname (hosp_name) || ' (
         pers Person, 
         cond preexisting_conditions)
         NESTED TABLE cond STORE AS cond_st';
END;

TIP: Since preexisting_conditions is a nested table, I must specify the "store table" that will hold it.

1. Add a "profit source" (formerly known as a "patient") to the hospital, including his or her preexisting conditions. Here's the implementation from the body:

PROCEDURE add_profit_source (
   hosp_name IN VARCHAR2, 
   pers IN Person, 
   cond IN preexisting_conditions)
IS
BEGIN
   EXECUTE IMMEDIATE 
      'INSERT INTO ' || tabname (hosp_name) || 
          ' VALUES (:revenue_generator, :revenue_inhibitors)' 
      USING pers, cond;
END;

The use of objects and collections is transparent. I could be inserting scalars like numbers and dates, and the syntax and code would be the same.

2. Remove from the hospital all the really poor and sick people, those individuals who are not contributing to the profit margins of my corporation (hey, maybe we shouldn't mix profit margins and health care!). This is the most complex of the programs; here is the implementation:

PROCEDURE weed_out_poor_and_sick (
   hosp_name VARCHAR2,
   min_income IN NUMBER := 100000,
   max_preexist_cond IN INTEGER := 1)
IS
   cv RefCurTyp;
   human Person;
   known_bugs preexisting_conditions;
   
   v_table VARCHAR2(30) := tabname (hosp_name);
   v_rowid ROWID;
BEGIN
   /* Find all rows with more than the specified number
      of preconditions and deny them coverage. */  
   OPEN cv FOR
      'SELECT ROWID, pers, cond
         FROM ' || v_table || ' alias
        WHERE (SELECT COUNT(*) FROM TABLE (alias.cond))
               > ' ||
               max_preexist_cond || 
         ' OR
               alias.pers.income < ' || min_income;        
   LOOP
      FETCH cv INTO v_rowid, human, known_bugs;
      EXIT WHEN cv%NOTFOUND; 
      EXECUTE IMMEDIATE 
         'DELETE FROM ' || v_table ||
         ' WHERE ROWID = :rid'
         USING v_rowid;
   END LOOP;
   CLOSE cv;
END;

TIP: I decided to retrieve the ROWID of each profit source so that when I do the DELETE it would be easy to identify the row. It would be awfully convenient to make the query FOR UPDATE, and then use WHERE CURRENT OF cv in the DELETE statement, but that is not possible, for two reasons: (1) The cursor variable would have to be globally accessible to be referenced inside a dynamic SQL statement, and (2) You cannot declare cursor variable in packages, because they don't have persistent state. See the later section called "Dynamic PL/SQL" for more details.

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.

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 "NDS Utility Package" follows this standard.

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 the later "NDS Utility Package" section).

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.

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.

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!

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

NDS Utility Package

To make it easier for my readers to take advantage of the various generic utilities discussed in this chapter, I have created a single package called ndsutil. This package, available on the companion disk in ndsutil.pkg, contains the programs listed in Table 4-1.

Table 4-1: Contents of the NDS Utility Package

Name	Description
execImmed	Substitute for EXECUTE IMMEDIATE that does not need a USING or INTO clause; includes error handling.
openFor	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 reraise the error, whereas functions generally return NULL or a NULL/empty structure.

TIP: I am sure there are many programs you can add to this package. Please post any additions to the PL/SQL Pipeline at http://www.revealnet.com/plsql-pipeline, in the Pipetalk area. Then we can all gain from your experience and creativity!

Back to: Oracle PL/SQL Programming Guide to Oracle8i Features