2.4.1 Some Restrictions

You can do a lot of awfully interesting stuff with DBMS_SQL, but some things are off-limits:

• You cannot manipulate cursor variables from within dynamic SQL. Cursor variables are a relatively new, advanced, and little-used feature of PL/SQL (see Chapter 6 of Oracle PL/SQL Programming for more information). But if you want to use them, you'll have to do it in static PL/SQL code.

• Unless otherwise noted, DBMS_SQL does not support many of the new data structures in Oracle8. For example, you cannot bind an object or a nested table or a variable array.

2.4.2 Privileges and Execution Authority with DBMS_SQL

There are two basic rules to remember when working with DBMS_SQL:

• Stored programs execute under the privileges of the owner of that program. So if you parse and execute dynamic SQL from within a program, references to database objects in that SQL statement are resolved according to the schema of the program, not the schema of the person running the program.

• Roles are disabled when compiling and executing PL/SQL code. Privileges must be granted directly in order to be used with PL/SQL code. So when you execute dynamic SQL from within a PL/SQL program, you must have directly granted privileges to any database objects referenced in the dynamically constructed string.

The following anecdotes demonstrate the kinds of problems you can face with dynamic SQL.

2.4.3 Combining Operations

Every PARSE must be preceded by a call to OPEN_CURSOR. Every call to PARSE must include a DBMS mode argument, even though 99.99% of the time, it is going to be DBMS_SQL.NATIVE.

When I find myself repeating the same steps over and over again in using a package or particular feature, I look for ways to bundle these steps into a single procedure or function call to save myself time. The next function shows such a function, open_and_parse, which opens a cursor and parses the specified SQL statement. Using open_and_parse, I can replace the following statements,

cursor_handle := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (cursor_handle, 'UPDATE emp ... ', DBMS_SQL.NATIVE);

with just this:

cursor_handle := open_and_parse ('UPDATE emp ... ');

Here, then, is the open_and_parse bundled procedure:

/* Filename  on companion disk: openprse.sf */*
CREATE OR REPLACE FUNCTION open_and_parse 
   (sql_statement_in IN VARCHAR2, 
    dbms_mode_in IN INTEGER := DBMS_SQL.NATIVE) 
RETURN INTEGER
IS
   /* Declare cursor handle and assign it a pointer */
   return_value INTEGER := DBMS_SQL.OPEN_CURSOR;
BEGIN
   /* Parse the SQL statement */
   DBMS_SQL.PARSE (return_value, sql_statement_in, dbms_mode_in);

   /* Pass back the pointer to this parsed statement */
   RETURN return_value;
END;

Now, one problem with this otherwise handy little procedure is that it always declares a new cursor. What if you already have a cursor? Then you should go straight to the parse step. You can combine the functionality of open_and_parse with the initcur procedure shown in the next section to produce your own enhanced program.

2.4.4 Minimizing Memory for Cursors

As noted earlier, you will not want to allocate a cursor via the OPEN_CURSOR procedure if you can instead use an already defined cursor that is not currently in use. The best way to minimize memory usage with dynamic SQL cursors is to encapsulate the open action inside a procedure. The initcur procedure shown below demonstrates this technique.

With initcur, you pass a variable into the procedure. If that variable points to a valid, open DBMS_SQL cursor, then it is returned unchanged. If, on the other hand, that cursor is closed (the IS_OPEN function returns FALSE) or IS_OPEN for any reason raises the INVALID_CURSOR exception, then OPEN_CURSOR is called and the new cursor pointer is returned.

CREATE OR REPLACE PROCEDURE initcur (cur_inout IN OUT INTEGER) IS
BEGIN
   IF NOT DBMS_SQL.IS_OPEN (cur_inout)
   THEN
      cur_inout := DBMS_SQL.OPEN_CURSOR;
   END IF;
EXCEPTION
   WHEN invalid_cursor
   THEN
      cur_inout := DBMS_SQL.OPEN_CURSOR;
END;
/

You could also implement this functionality as a function, or could overload both inside a package, as follows:

CREATE OR REPLACE PACKAGE dyncur
IS
    PROCEDURE initcur (cur_inout IN OUT INTEGER);
    FUNCTION initcur (cur_in IN INTEGER) RETURN INTEGER;
END dyncur;
/

In addition to allocating cursor areas only when necessary, you should make sure that you close your cursors when you are done with them. Unlike static cursors, which close automatically when their scope terminates, a dynamic SQL cursor will remain open even if the block in which it was defined finishes execution. And remember that you should perform the close operation at the end of the executable code, but also in any exception sections in the block. This technique is shown here:

CREATE OR REPLACE PROCEDURE do_dynamic_stuff
IS
   cur1 INTEGER := DBMS_SQL.OPEN_CURSOR;
   cur2 INTEGER := DBMS_SQL.OPEN_CURSOR;

   PROCEDURE closeall IS
   BEGIN
      /* Only close if open. Defined in Closing the Cursor section. */
      closeif (cur1);
      closeif (cur2);
   END;
BEGIN
   /* Do the dynamic stuff, then close the cursors.*/
   ...
   closeall;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      /* Special handling, then cleanup */
      ...
      closeall;

   WHEN OTHERS
   THEN
      /* Catch-all cleanup, then reraise to propagate out the error.*/
      closeall;
      RAISE;
END;
/   

The openprse.ssp file contains a package that implements both the initcur and an enhanced version of open_and_parse (see the section called Section 2.4.3, "Combining Operations"). This package allows you to keep to an absolute minimum the number of cursors allocated to perform dynamic SQL operations.

2.4.5 Improving the Performance of Dynamic SQL

You can improve the performance of your dynamic SQL operations by taking advantage of two aspects of DBMS_SQL:

• Reuse DBMS_SQL cursors whenever possible (this technique was demonstrated in the last section).

• Avoid reparsing your dynamic SQL when the only thing that changes is a bind variable.

The following script (written with the help of John Beresniewicz) demonstrates the gains you can see by paying attention to both of these considerations. The script illustrates three different ways to do the same thing in dynamic SQL using DBMS_SQL, namely fetch rows from a table.

• Approach 1 parses and executes each query without using any bind variables. It is the most inefficient, performing 1000 parses, executing 1000 statements, and requiring room in the shared pool for up to 1000 different SQL statements.

• Approach 2 parses and executes for each query, but uses bind variables, so it works a bit more efficiently. With this approach, you still perform 1000 parses and execute 1000 times, but at least you need room for only one SQL statement in the shared pool.

• Approach 3 parses just once and executes each query using host variables. It is by far the most efficient technique: requiring just one parse, 1000 executions, and a single preparsed SQL statement in the shared pool.

I have used a very large and cumbersome SELECT in this test to make sure that there was enough overhead in parsing to both simulate a "real-world" query and also to demonstrate a clear difference in performance between the second and third approaches (for very simple SQL statements, you will not see too much of a difference). For the sake of brevity, I will not show the entire query in the code.

/* Filename on companion disk: effdsql.tst */*
DECLARE
/*
|| Approach 1: the worst
*/
   v_start INTEGER;
   cursor_id INTEGER;
   exec_stat INTEGER;
BEGIN
   v_start := DBMS_UTILITY.GET_TIME;
   cursor_id := DBMS_SQL.OPEN_CURSOR;
   FOR i IN 1..&1
   LOOP
      /* 
      || parse and excecute each loop iteration
      || without using host vars, this is worst case
      */
      DBMS_SQL.PARSE (cursor_id, 'SELECT ...', DBMS_SQL.native);
      exec_stat := DBMS_SQL.EXECUTE(cursor_id);
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(cursor_id);
   DBMS_OUTPUT.PUT_LINE 
      ('Approach 1: ' || TO_CHAR (DBMS_UTILITY.GET_TIME - v_start));
END;
/
DECLARE
/*
|| Approach 2: a little better
*/
   v_start INTEGER;
   cursor_id INTEGER;
   exec_stat INTEGER;
BEGIN
   v_start := DBMS_UTILITY.GET_TIME;
   cursor_id := DBMS_SQL.OPEN_CURSOR;
   FOR i IN 1..&1
   LOOP
      /* 
      || parse and excecute each loop iteration using host vars
      */
      DBMS_SQL.PARSE (cursor_id, 'SELECT ...', DBMS_SQL.native);
      DBMS_SQL.BIND_VARIABLE(cursor_id,'i',i);
      exec_stat := DBMS_SQL.EXECUTE(cursor_id);
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(cursor_id);
   DBMS_OUTPUT.PUT_LINE 
      ('Approach 2: ' || TO_CHAR (DBMS_UTILITY.GET_TIME - v_start));
END;
/
DECLARE
/*
|| Approach 3: the best
*/
   v_start INTEGER;
   cursor_id INTEGER;
   exec_stat INTEGER;
BEGIN
   v_start := DBMS_UTILITY.GET_TIME;
   cursor_id := DBMS_SQL.OPEN_CURSOR;
   /*
   || Parse first, outside of loop
   */
   DBMS_SQL.PARSE (cursor_id, 'SELECT ...', DBMS_SQL.native);
   FOR i IN 1..&1
   LOOP
      /* 
      || bind and excecute each loop iteration using host vars
      */
      DBMS_SQL.BIND_VARIABLE(cursor_id,'i',i);
      exec_stat := DBMS_SQL.EXECUTE(cursor_id);
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(cursor_id);
   DBMS_OUTPUT.PUT_LINE 
      ('Approach 3: ' || TO_CHAR (DBMS_UTILITY.GET_TIME - v_start));
END;
/

And here are the results from running this script twice:

SQL>  @effdsql.tst 10000
Approach 1: 860
Approach 2: 981
Approach 3: 479

2.4.6 Problem-Solving Dynamic SQL Errors

Sometimes the hardest aspect to building and executing dynamic SQL programs is getting the string of dynamic SQL right. 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 together, getting the commas right, and the ANDs and ORs right, and so on. What happens if you get it wrong? Well, let's take the nightmare scenario and work it through.

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 dynamic SQL. I can, in a flash, type OPEN_CURSOR, PARSE, DEFINE_COLUMN, and other commands. I know the right sequence, I know how to detect when there are no more rows to fetch, and I blast through the development phase. I also rely on some standard exception-handling programs I have built that display an error message when 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. 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
ORA-00911: invalid character

Ugh. A whole bunch of error messages, clearly showing that various SQL statements have been constructed improperly and are causing parse errors -- but which SQL statements are the troublemakers? That is a very difficult question to answer. One way to get at the answer is to place all calls to the PARSE procedure inside an exception section and then display the string causing the error.

CREATE OR REPLACE PROCEDURE whatever
IS
   v_sql VARCHAR2(32767);
BEGIN
   construct_sql (v_sql);

   DBMS_SQL.PARSE (cur, v_sql, DBMS_SQL.NATIVE);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Error in ' || v_sql);
END;
/

This certainly would have helped explain those earlier error messages. The problem with this approach is that I would need to build this exception section every time I call PARSE. I also might be raising exceptions from lines of code other than those containing the call to PARSE. How could I distinguish between the errors and the information I should display? Furthermore, I might discover after writing the previous code ten or twenty times that I need more information, such as the error code. I would then have to go back to all those occurrences and enhance them. This is a very tedious, high-maintenance, and generally nonproductive way of doing things.

A different and better approach is to provide your own substitute for PARSE that encapsulates, or hides away, all of these details. You don't have to add exception sections in each call to this substitute, because it would come with its own exception section. And if you decide you want to do things differently, you just change this one program. Doesn't that sound so much better?

Let's go through the steps involved in creating a layer over PARSE that enhance its error-detection capabilities. First, we will build the interface to the underlying DBMS_SQL call. That is easy enough:

/* Filename on companion disk: dynsql.spp */*
/* Final version of package */
CREATE OR REPLACE PACKAGE dynsql
IS
   PROCEDURE parse 
      (cur IN INTEGER, 
       sqlstr IN VARCHAR2, 
       dbmsmode IN INTEGER := NULL);
END;
/ 

Why did I bother to put this single procedure inside a package? I always start with packages, because sooner or later I want to add more related functionality, or I need to take advantage of package features, like persistent data. In this case, I could foresee providing an overloaded parse function, which opens and returns a cursor. I also expect to be defining some package data pertaining to error information, which would require a package.

Notice that the parse procedure looks just like the DBMS_SQL version, except that the database mode has a default value of NULL (which will translate into DBMS_SQL.NATIVE). This way (a) you do not have to bother with providing a mode, and (b) the default value is not a packaged constant, which could cause problems for calling this program from within Oracle Developer Release 1.

It would be a good idea to compare using DBMS_SQL with dynsql before we even try to implement this package; that will be a validation of the design of the interface. So instead of this,

DECLARE
   cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
   fdbk PLS_INTEGER;
BEGIN
   DBMS_SQL.PARSE (cur, 'CREATE INDEX ... ', DBMS_SQL.NATIVE);

I could use dynsql.parse as follows:

DECLARE
   cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
BEGIN
   dynsql.parse (cur, 'CREATE INDEX ... ');

I get to write a little bit less code, but that isn't really the main objective. I just want to make sure that I can do whatever I can do with DBMS_SQL (with parse, anyway) through dynsql. Now let's build the package body and add some value:

CREATE OR REPLACE PACKAGE BODY dynsql
IS
   PROCEDURE parse 
      (cur IN INTEGER, sqlstr IN VARCHAR2, dbmsmode IN INTEGER := NULL)
   IS
   BEGIN
      DBMS_SQL.PARSE (cur, sqlstr, NVL (dbmsmode, DBMS_SQL.NATIVE));
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.PUT_LINE ('Error in ' || sqlstr);
   END;
END;
/    

With this program installed, I can replace all calls to PARSE with dynsql.parse and then see precisely which dynamic SQL statements are causing me problems. As I mentioned earlier, though, I really want to get more information. Suppose, for example, that I needed to see the error number (as surely I would), as well as the position in the SQL statement in which the error was detected. No problem! I just go to the package body and add a couple lines of code:

CREATE OR REPLACE PACKAGE BODY dynsql
IS
   PROCEDURE parse 
      (cur IN INTEGER, sqlstr IN VARCHAR2, dbmsmode IN INTEGER := NULL)
   IS
   BEGIN
      DBMS_SQL.PARSE (cur, sqlstr, NVL (dbmsmode, DBMS_SQL.NATIVE));
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.PUT_LINE ('Parse error: ' || TO_CHAR (SQLCODE) ||
            ' at position ' || TO_CHAR (DBMS_SQL.LAST_ERROR_POSITION));
         DBMS_OUTPUT.PUT_LINE ('SQL string: ' || sqlstr);
   END;
END;
/    

This should put me in good stead, except for one problem: what if my SQL string is more than 243 bytes in length? The PUT_LINE procedure will raise a VALUE_ERROR if the string passed to it exceeds 255 bytes in length. What an annoyance! But since I have had the foresight to hide all my calls to PARSE away in this single program, I can even address this difficulty. PL/Vision Lite[1] offers a display_wrap procedure in the PLVprs package. So I can avoid any VALUE_ERROR exceptions as follows:

[1] This software comes with my book Advanced Oracle PL/SQL Programming with Packages (O'Reilly & Associates, 1996). You can also download it from http://www.revealnet.com.

CREATE OR REPLACE PACKAGE BODY dynsql
IS
   PROCEDURE parse 
      (cur IN INTEGER, sqlstr IN VARCHAR2, dbmsmode IN INTEGER := NULL)
   IS
   BEGIN
      DBMS_SQL.PARSE (cur, sqlstr, NVL (dbmsmode, DBMS_SQL.NATIVE));
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.PUT_LINE ('Parse error: ' || TO_CHAR (SQLCODE) ||
            ' at position ' || TO_CHAR (DBMS_SQL.LAST_ERROR_POSITION));
         PLVprs.display_wrap ('SQL string: ' || sqlstr);
   END;
END;
/    

See how easy it is to upgrade your programs and fix shortcomings once you have encapsulated your repetitive actions behind a programmatic interface?

2.4.7 Executing DDL in PL/SQL

DBMS_SQL allows you to execute almost any DDL statements from within PL/SQL. Here are some considerations to keep in mind:

• You should explicitly execute and then close your DDL cursors. Currently, Oracle will automatically execute DDL statements when they are parsed with a call to PARSE. Oracle Corporation warns users of DBMS_SQL that this behavior might not be supported in the future.

• You cannot establish a new connection to Oracle through PL/SQL. You cannot, in other words, issue a CONNECT command from within PL/SQL; you will get an "ORA-00900: invalid SQL statement" error. From this, one can deduce that CONNECT is not a SQL statement. It is, rather, a SQL*Plus command.

• You must have the necessary privileges to execute that DDL statement granted explicitly to the account owning the program in which the DDL is being run. Remember that roles are disabled during PL/SQL compilation and execution. If you want to create a table using dynamic SQL, you must have CREATE TABLE or CREATE ANY TABLE privileges granted directly to your schema.

• Your dynamic DDL execution can result in your program hanging. When I call a procedure in a package, that package is locked until execution of that program ends. If another program attempts to obtain a conflicting lock (this might occur if you try to drop that package using dynamic DDL), that program will lock waiting for the other program to complete execution.

2.4.8 Executing Dynamic PL/SQL

Dynamic PL/SQL is an awful lot of fun. Just think: you can construct your PL/SQL block "on the fly" and then execute it from within another PL/SQL program. Here are some factors to keep in mind as you delve into this relatively esoteric aspect of PL/SQL development:

• The string you execute dynamically must start with a DECLARE or BEGIN statement and terminate with "END." It must, in other words, be a valid anonymous block.

• The string must end with a semicolon, unlike DDL and DML statements, which cannot end with a semicolon.

• The dynamic PL/SQL block executes outside the scope of the block in which the EXECUTE function is called, but that calling block's exception section will trap exceptions raised by the dynamic PL/SQL execution.

• As a direct consequence of the previous rule, you can only reference globally available data structures and program elements from within the dynamic PL/SQL block.

Let's explore those last two restrictions 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 dyn_plsql (blk IN VARCHAR2)
IS
   cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
   fdbk PLS_INTEGER;
BEGIN
   DBMS_SQL.PARSE (cur,
      'BEGIN ' || RTRIM (blk, ';') || '; END;',
      DBMS_SQL.NATIVE);

   fdbk := DBMS_SQL.EXECUTE (cur);
   DBMS_SQL.CLOSE_CURSOR (cur);
END;
/

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

SQL> exec dyn_plsql ('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   
   dyn_plsql ('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! */
   dyn_plsql ('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 then able to execute the dynamic assignment to this newly defined variable successfully.

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

What's the difference between these two pieces of data? In the first attempt, the variable num is defined locally in the anonymous PL/SQL block. In my second attempt, num is a public "global" 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 like 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 stand alone 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
   dyn_plsql ('undefined.packagevar := ''abc''');
EXCEPTION
   WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (sqlcode);
END;
/

I will not get an unhandled exception.

The dynpl/sql.tst file compares the performance of static PL/SQL execution (assigning a value to a global variable) with dynamic PL/SQL.