19.5.5.1 Implementation of PLVdyn.plsql

The implementation of plsql, shown below, is a good example of how you can build additional smarts into your software so that it is easier and more intuitive to use. It also demonstrates how I combine other, bundled operations to construct higher-level programs (these bundled operations are explored in a later section).

PROCEDURE plsql (string_in IN VARCHAR2)
IS
   cur INTEGER := 
      open_and_parse (plsql_block (string_in));
BEGIN
   execute_and_close (cur);
END;

The plsql_block function is designed specifically to return a valid PL/SQL block as follows:

FUNCTION plsql_block (string_in IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
   RETURN 'BEGIN ' || RTRIM (string_in, ';') || '; END;';
END;

NOTE: The plsql procedure assumes that you do not have any bind variables. If a colon appears in the PL/SQL code and it is not within single quotes (part, that is, of a literal string), PLVdyn.plsql will not be able to execute your statement successfully.

19.5.5.2 Scope of a dynamic PL/SQL block

Precisely what kind of PL/SQL code can you execute with DBMS_SQL? The answer is not as simple as it might seem at first glance. The rule you must follow is this:

A dynamically-constructed PL/SQL block can only execute programs and reference data structures which are defined in the specification of a package.

Consider, for example, the following simple script:

<<dynamic>>
DECLARE
   n NUMBER;
BEGIN   
   PLVdyn.plsql ('n := 5');
END;
/   

All I am doing is assigning a value of 5 to the local variable n. This string is executed within its own BEGIN-END block, that 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 'N' 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 n. I get the same error even if I qualify the variable name with its block name:

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

Yet if instead of modifying the value of n, I modify the PLV.plsql_identifier variable as shown below, I am able to execute the dynamic assignment successfully.

<<dynamic>>
DECLARE
   n NUMBER;
BEGIN   
   PLVdyn.plsql ('PLV.plsql_identifier := ''5''');
END;
/   

What's the difference between these two pieces of data? The variable n is defined locally in the anonymous PL/SQL block. The plsql_identifier variable is a public global defined in the PLV 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 standalone functions and procedures and any element defined in the specification of a package. That is why my reference to the plsql_identifier variable of the PLV package passed without error. It is defined in the package specification and is globally available.

Fortunately, the dynamic block is executed within the context of the calling block. If you have an exception section within the calling block, it traps exceptions raised in the dynamic block.

19.5.5.3 The overhead of dynamic PL/SQL

As soon as you move to dynamic processing, you can expect that there will be some overhead associated with the extra work performed (construct the string, parse the string, and -- with dynamic PL/SQL -- compile the anonymous block). Dynamic PL/SQL is really neat stuff -- but is it practical? What is the performance penalty when executing, for example, the PLVdyn.plsql procedure?

I came up with an answer by using the PLVtmr package (see Chapter 14, PLVtmr: Analyzing Program Performance). The script below first determines how much time it takes to do, well, nothing with a call to the NULL statement. This provides a baseline for static code execution (and you can't get much more base than that). I then execute the NULL statement dynamically, with a call to the PLVdyn.plsql procedure. The single substitution parameter, &1, allows me to specify the number of iterations for the test.

BEGIN
   PLVtmr.capture;
   FOR rep IN 1 .. &1
   LOOP
      NULL;
   END LOOP;
   PLVtmr.show_elapsed ('static');

   PLVtmr.capture;
   FOR rep IN 1 .. &1
   LOOP
      PLVdyn.plsql ('NULL');
   END LOOP;
   PLVtmr.show_elapsed ('dynamic');
END;
/

I then executed this script in SQL*Plus several times to make sure my results were consistent:

SQL> @temp 100
static Elapsed: 0 seconds.
dynamic Elapsed: 1.38 seconds.
SQL> @temp 1000
static Elapsed: .11 seconds.
Dynamic Elapsed: 13.57 seconds.
SQL> set verify off
SQL> @temp 1000
static Elapsed: .16 seconds.
dynamic Elapsed: 13.41 seconds.

I conclude, therefore, that the overhead of constructing, compiling, and executing a dynamic block of PL/SQL code is at least (and approximately) .0133 seconds. I say "at least" because if your PL/SQL block is bigger (consisting of more than, say, a single statement or call to a stored procedure), your overhead increases. It looks to me that for most situations the additional processing time required for dynamic PL/SQL should not deter you from using this technique.

19.5.6.1 Open and parse

The open_and_parse function calls DBMS_SQL.OPEN_CURSOR and DBMS_SQL.PARSE. These are both mandatory steps for any method of dynamic SQL you choose to implement with DBMS_SQL. So rather than have to call these two separate programs over and over again, you can simply call the single function. In addition, the function hides the need to pass the database version (in the form of a constant such DBMS_SQL.V7) when you call PARSE. The header for open_and_parse is:

FUNCTION open_and_parse 
   (string_in IN VARCHAR2 SEF,
    mode_in IN INTEGER := DBMS_SQL.NATIVE) 
RETURN INTEGER

With this bundled operation, you can open and parse a SQL statement with the following single line of code:

cur := PLVdyn.open_and_parse (string_in);

Without open_and_parse, you type the following lines of code:

cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (cur, string_in, DBMS_SQL.V7);

PLVdyn programs themselves make use of open_and_parse. Any time you parse a SQL statement through PLVdyn, you are, in fact, executing open_and_parse. This bundled program therefore acts as a kind of gateway through which all dynamic SQL must pass. I took advantage of this fact when I constructed a trace or window for PLVdyn (covered later in this chapter).

19.5.6.2 Execute and close

In many (but not all) situations, you want to execute your cursor and then immediately close it. If you are executing an INSERT, UPDATE, or DELETE, for example, you do not have to fetch rows from the SQL statement. After executing, you are ready to close.

The header for execute_and_close is as follows:

PROCEDURE execute_and_close (cur_inout IN OUT INTEGER)

In other words, you pass in the cursor and PLVdyn does the rest for you. Using this bundled operation, you can execute and close with this single line:

PLVdyn.execute_and_close (cur);

Otherwise, you have to code these lines (and declare a variable to hold the feedback from the EXECUTE statement):

fdbk := DBMS_SQL.EXECUTE (cur);
DBMS_SQL.CLOSE (cur);

With PLVdyn, you don't have to deal with the EXECUTE feedback unless you want to. If you need to know the results of the operation, you can access it through the dml_result or fdbk functions. These programs return the feedback from the most recent execution of execute_and_close.

By calling PLVdyn.execute_and_close instead of DBMS_SQL.EXECUTE, you can also take advantage of the execute toggle in PLVdyn, which is covered below.

19.5.6.3 Parse for delete

The last bundled operation is parse_delete. It is a bit more complex than the other operations. It is used by the dml_delete procedure to construct the SQL DELETE and then parse it, as shown below:

PROCEDURE dml_delete
   (table_in IN VARCHAR2, where_in IN VARCHAR2 := NULL)
IS
   cur INTEGER;
BEGIN
   parse_delete (table_in, where_in, cur);
   execute_and_close (cur);
END;

You might ask why I built a separate parse_delete procedure for this single call in dml_delete (it is not used anywhere else in PLVdyn). That is a reasonable question to ask. After all, one should be careful not to over-modularize. You can argue that it never hurts to break out functionality into separate programs. Then it is at least potentially reusable. You could, however, spend (waste?) many hours constructing individual modules that are never actually reused. Find the right balance.

In the case of parse_delete, it only looks as if the procedure is used once. In fact, parse_delete is also used in a second dynamic SQL package, PLVdyn1 (the code for which is found at the end of this chapter). This package contains a series of overloaded delete programs that take a single bind variable as input. For example, I could delete all employees where the hiredate is more than 100 years in the past with this call:

PLVdyn1.dml_delete
  ('emp', 
   ADD_MONTHS (SYSDATE, -1200),
   'hiredate < :indate');

So I do, in fact, need a consolidated parse_delete function to handle common operations across at least four different delete programs. Now that I have justified my development process to my readers, let's take a look at the specification for parse_delete:

PROCEDURE parse_delete
   (table_in IN VARCHAR2, 
    where_in IN VARCHAR2, 
    cur_out OUT INTEGER);

You pass a table and the WHERE clause and receive a cursor handle in return. You can then use this cursor handle in a call to execute_and_close. The implementation of parse_delete is shown below. It constructs the DELETE string based on the inputs and then calls the prebuilt open_and_parse (notice that it does not need the "PLVdyn" prefix on the call to open_and_parse, since it is coded within the package).

PROCEDURE parse_delete
  (table_in IN VARCHAR2, 
   where_in IN VARCHAR2, 
   cur_out OUT INTEGER)
IS
   delete_stg PLV.dbmax_varchar2%TYPE := 'DELETE FROM ' || table_in;
   cur INTEGER;
BEGIN
   IF where_in IS NOT NULL
   THEN
      delete_stg := delete_stg || ' WHERE ' || where_in;
   END IF;
   cur_out := open_and_parse (delete_stg);
END;

These low-level, bundled operations are very handy when constructing more complex, full-featured dynamic SQL programs. Consider the ddl procedure described earlier in Section 19.5, "DML Operations". The implementation of PLVdyn.ddl is shown below:

PROCEDURE ddl (string_in IN VARCHAR2)
IS
   cur INTEGER;
BEGIN
   IF NOT executing
   THEN
      p.l ('PLVdyn: No parse/execute of DDL.');
      display_dynamic_sql (string_in);
   ELSE
      cur := open_and_parse (string_in);
   END IF;
END;

The executing function and the display_dynamic_sql support the execution toggle and execution trace features of PLVdyn. Disregarding those lines for the time being, the entire body of the ddl procedure is simply:

cur := open_and_parse (string_in);

Since it is a DDL statement, the parse automatically executes and commits, so this is all the code that is needed. The bundled operations (open_and_parse, execute_and_close, and so on) are called throughout the body of the PLVdyn package to minimize code volume and maximize code reuse.

19.5.6.4 Passthrough elements

PLVdyn offers two execute-related programs that are nothing more than passthroughs to the corresponding DBMS_SQL element. The headers for these passthroughs are:

PROCEDURE execute (cur_in IN INTEGER);
PROCEDURE execute_and_fetch
   (cur_in IN INTEGER, match_in IN BOOLEAN := FALSE);

PLVdyn.execute calls the DBMS_SQL.EXECUTE builtin, while PLVdyn.execute_and_fetch calls the DBMS_SQL.EXECUTE_AND_FETCH builtin.

Why did I bother building these procedures and why should you bother to use them? There are two basic reasons:

1. Ease of use. The DBMS_SQL versions of these programs are functions. They return the number of rows affected by the SQL statement executed. This is important information, but it is not always needed. With PLVdyn passthroughs, you can simply call the procedure to perform the execute. If you then need to see the feedback value, you can call the PLVdyn.fdbk function.

2. PLVdyn has placed a layer of code between your program and the native builtin function. Once this layer is in place, PLVdyn can add functionality around the DBMS_SQL execute operation. The execsql toggle, for example, allows you to turn on or off the actual execution of your dynamic SQL -- without having to change your application's code. PLVdyn could also enhance the trace to show you when code is executed.

19.5.9.1 Using the dynamic SQL trace

In the following script, I turn on the trace and then execute a request to open and parse an INSERT statement.

SET SERVEROUTPUT ON
execute PLVdyn.showsql;

DECLARE
   cur INTEGER;
BEGIN
   cur := PLVdyn.open_and_parse
     ('insert into emp (empno, deptno) values (1505, 100)');
END;
/

I then receive this output:

Dynamic SQL: insert into emp (empno, deptno) values (1505, 100)

The trace comes in especially handy when you encounter errors in your dynamic SQL execution. Suppose you make extensive use of PLVdyn in your application. You start testing the code and have tossed in your face the following exception:

ORA-06510: PL/SQL: unhandled user-defined exception

You can turn on logging or displaying of exceptions if you are using PLVexc. Or you can simply issue this command:

PLVdyn.showsql;

and then re-execute your application. Suddenly, every time you parse SQL through PLVdyn, you see the SQL statement appear on the screen. When the problematic SQL fails, you will be able to identify the text and, from that, the part of your code that constructs that dynamic SQL string. Moving from that point to a solution is usually fairly straightforward.

The trace facility of PLVdyn illustrates some important principles of both generic package structure and high-quality reusable code. First, the public-private nature of the package allows me to construct a window into PLVdyn. This window offers a very controlled glimpse into the interior of the package. I let developers view the dynamic SQL string, but they can't look at or do anything else. This level of control allows Oracle to give us all of those wonderful builtin packages like DBMS_SQL and DBMS_PIPE. And it lets developers provide reusable PL/SQL components to other developers without fearing corruption of internal data structures.

See Chapter 2, Best Practices for Packages, for more information on windows into packages.

19.5.10.1 Checking the indexes

The DBA is bewildered and quickly runs the following script to examine the indexes on the emp table:

REM vuindex.sql
SELECT i.index_name, i.tablespace_name, 
       uniqueness u, column_name, column_position pos
  FROM all_indexes i, all_ind_columns c
 WHERE i.index_name = c.index_name
   AND i.table_name = upper ('&1'); 
SQL> start vuindex emp
INDEX_NAME          TABLESPACE_NAME      U         COLUMN_NAME          POS
------------------- -------------------- --------- -------------------- ---
EMP_PRIMARY_KEY     USER_DATA            UNIQUE    EMPNO                  1

There is no empname_idx index! What has gone wrong? On a hunch, the DBA connects to the PLVISION account, re-executes the same script, and sees these results:

INDEX_NAME          TABLESPACE_NAME      U         COLUMN_NAME          POS
------------------- -------------------- --------- -------------------- ---
EMPNAME_IDX         USER_DATA            NONUNIQUE ENAME                  1
EMP_PRIMARY_KEY     USER_DATA            UNIQUE    EMPNO                  1

The index was created in the PLVISION schema! Why did this happen?

When the DBA executed the index creation statement from within a call to PLVdyn.ddl, the DDL statement was processed as though it were being executed by PLVISION, not by SCOTT. If the DBA had wanted to create an index in her own schema, she should have entered the following command:

PLVdyn.ddl ('create index SCOTT.ename_idx on SCOTT.emp(ename)');

19.5.10.2 Avoiding schema confusion for DDL

It is very difficult to make sure that all developers who use PLVdyn remember this unusual twist. As a result, you may want to treat PLVdyn differently in terms of how you make it available to developers.

Ideally, one could enhance a package like PLVdyn to make sure that the correct schema (that returned by a call to USER) is applied to all DDL. As far as I can tell, however, that is a near-impossible task. How can a PL/SQL program parse the SQL statement and figure out where a schema must be added?

The alternative is ugly, but practical. Instead of creating the package in one account and granting execute authority to all others, consider creating the PLVdyn package directly in the accounts of each of the users of the package. This creates something of a maintenance headache, but it also guarantees that any SQL and PL/SQL developers run dynamically run under their own schema.

A final approach might be to strictly limit who has access to PLVdyn; perhaps it can only be executed through an interface that guarantees that schemas are applied correctly in the SQL before it is passed to PLVdyn for parsing.