#

| Chapter 19 PLVdyn and PLVfk: Dynamic SQL and PL/SQL |

## 19.5 DML Operations

Although it is possible to execute DML statements with static strings in PL/SQL, the dynamic nature of DBMS_SQL does offer many new opportunities. The PLVdyn packages offers a programmatic interface for several common DML operations to make it easier for you to take advantage of this technology.

PLVdyn offers three different DML operations: `dml_insert_select`, `dml_delete`, and `dml_update`. There are many other possibilities for dynamic DML in PLVdyn; I encourage you to experiment with your own extensions to this package.

> NOTE: You cannot include bind variables for any of the DML programs in PLVdyn. Generally, PLVdyn does not support bind variables. The consequence of this is that the WHERE clauses may not contain any colons unless they are embedded in a literal string. The PLVdyn1 package (described on the companion disk) does allow you to specify single bind variable values for a variety of DML statements.

### 19.5.1 Performing an INSERT-SELECT FROM

You can execute an INSERT-SELECT FROM statement using the `dml_insert_select`. The header for this procedure is:

>     PROCEDURE dml_insert_select
>        (table_in IN VARCHAR2, select_in IN VARCHAR2);

In the first argument you provide the name of the table that is going to receive the rows from the SELECT statement. The second argument is the SELECT statement itself. Here is an example of using this procedure to copy current invoices to a history table.

>     PLVdyn.dml_insert_select
>        ('inv_history',
>         'SELECT * FROM inv_current' ||
>         ' WHERE inv_date < ' || TO_CHAR (v_enddate));

### 19.5.2 Executing dynamic DELETEs

The `dml_delete` procedure allows you to delete from any table. The header is:

>     PROCEDURE dml_delete
>        (table_in IN VARCHAR2, where_in IN VARCHAR2 := NULL);

The first argument is the table from which rows are to be deleted. The second argument is an optional WHERE clause, which restricts the number of rows deleted. As with static SQL, the simplest form of a call to `dml_delete` (a NULL WHERE clause) results in the largest number of rows deleted.

The following call to `dml_delete` removes all employees in department 10 from the `emp` table:

>     PLVdyn.dml_delete ('emp', 'deptno = 10');

Clearly, this syntax offers very little in the way of productivity enhancement over simply executing this SQL statement:

>     SQL> delete from emp where deptno=10;

The big news about PLV`dyn.dml_delete` is that you can execute it from within a PL/SQL environment.

### 19.5.3 Executing dynamic UPDATEs

The `dml_update` procedure is overloaded to allow you to easily perform updates of single columns of date, number or string datatypes. Here is the overloaded header for `dml_update`:

>     PROCEDURE dml_update
>        (table_in IN VARCHAR2,
>         column_in IN VARCHAR2,
>         value_in IN VARCHAR2|DATE|NUMBER,
>         where_in IN VARCHAR2 := NULL);

The only optional argument is the WHERE clause. If you do not supply a WHERE clause, the requested UPDATE will be performed for all rows in the table.

The following examples demonstrate the different ways to use `dml_update`.

1. Set the salary of all employees to $100,000.

   >     PLVdyn.dml_update ('emp', 'sal', 100000);

2. Set the last updated time stamp to SYSDATE for all invoices with creation dates in the last three months.

   >     PLVdyn.dml_update
   >        ('invoice', 'update_ts', SYSDATE,
   >         'create_ts >= ADD_MONTHS(SYSDATE,-3)');

### 19.5.4 Checking feedback on DML

Each of the PLVdyn DML programs calls the `PLVdyn.execute` procedure, which, in turn, calls the DBMS_SQL.EXECUTE builtin to actually execute the SQL statement. DBMS_SQL.EXECUTE is a function that returns an integer, telling you the number of rows affected by the SQL statement.

PLVdyn saves and hides this feedback value to make it easier to execute your SQL. You can, however, check the result of your latest dynamic execution by calling the `dml_result` function. In the example following, I delete rows from the `emp` table and then check to see how many rows were actually deleted.

>     BEGIN
>        PLVdyn.dml_delete ('emp');
>        IF PLVdyn.dml_result > 100
>        THEN
>           /*
>           || Too many deletes. There is some kind
>           || of data problem. Issue a rollback.
>           */
>           PLVrb.do_rollback;
>        END IF;

### 19.5.5 Executing Dynamic PL/SQL Code

PLVdyn makes it easy for you to execute dynamically constructed PL/SQL statements with the `plsql` procedure. The header for this program is:

>     PROCEDURE plsql (string_in IN VARCHAR2);

You construct a PL/SQL statement or block of statements and then pass that string to `PLVdyn.plsql`. It then executes that code. By using `PLVdyn.plsql`, you do not have to code the separate open, parse, and execute steps that are required with dynamic PL/SQL. In addition, this procedure relieves you of the burden of dealing with the following rules about dynamic PL/SQL:

1. The statement must end in a semicolon.

2. The statement must be a valid PL/SQL block. It must begin, in other words, with either the DECLARE or BEGIN keywords, and end with the END; statement.

You can provide to `PLVdyn.plsql` a string that meets these requirements, or you can ignore those requirements and `plsql` takes care of things for you. As a result, any of the following calls to `PLVdyn.plsql` properly executes the `calc_totals` procedure:

>     SQL> exec PLVdyn.plsql ('calc_totals');
>     SQL> exec PLVdyn.plsql ('calc_totals;');
>     SQL> exec PLVdyn.plsql ('BEGIN calc_totals; END;');

The `plsql` procedure accomplishes this by stripping off any trailing semicolons and wrapping your string inside a BEGIN-END block, regardless of whether or not you have already done so.

#### 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 Bundled and Passthrough Elements

While PLVdyn does offer a number of useful, high-level operations to perform dynamic SQL, there is no way that I can build enough of these kinds of programs to handle all dynamic SQL needs. I can, however, still add value for a developer who has very unique requirements: I can bundle together common, often-repeated steps into single lines of code. The developer still has to write his or her own full-fledged dynamic SQL program, but can rely on these lower-level "prebuilts" (as opposed to "builtins") to improve productivity and code quality.

The PLVdyn package offers these bundled elements:

`open_and_parse`

  Performs the open and parse phases.

`execute_and_close`

  Executes and closes the cursor.

`parse_delete`

  Constructs and parses a DELETE statement from its components.

PLVdyn adds value in one other way: with passthrough programs. These passthroughs do not (in normal operation) necessarily do anything but execute their corresponding DBMS_SQL program. Since they are a layer of code around the builtins, however, the passthroughs provide an opportunity to add functionality in other ways.

The passthroughs of PLVdyn are `execute` and `execute_and_fetch`; they are discussed after the bundled elements below.

#### 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 PLV`dyn.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.7 Displaying a Table

PLVdyn offers the `disptab` procedure to display the contents of any database table from within the PL/SQL environment. This procedure is both a useful utility (one that is used by PLVlog, as is shown below) and an excellent demonstration of how to build programs that support Method 4 dynamic SQL with the DBMS_SQL package.

The header of `disptab` follows:

>        PROCEDURE disptab
>          (table_in IN VARCHAR2,
>           string_length_in IN INTEGER := 20,
>           where_in IN VARCHAR2 := NULL,
>           date_format_in IN VARCHAR2 := PLV.datemask);

The four arguments to `disptab` are as follows:

`table_in`

  The name of the table to be displayed. This is the only required argument.

`string_length_in`

  The maximum number of characters to be used in displaying string columns. I do not even attempt to do the kind of string wrapping performed in SQL*Plus. Instead, I use SUBSTR to truncate the values.

`where_in`

  An optional WHERE clause to restrict the rows displayed. If not specified, all rows are retrieved. You can also use this argument to pass in ORDER BY and HAVING clauses, since they follow immediately after the WHERE clause.

`date_format_in`

  The format to be used to display dates. The default is the default date mask for PL/Vision, maintained in the PLV package.

Here are some examples of output from `disptab`:

1. Display the full contents of the employee table:

>     SQL> execute PLVdyn.disptab ('emp');

>     -----------------------------------------------------------------------
>                                Contents of emp
>     -----------------------------------------------------------------------
>     EMPNO ENAME      JOB       MGR  HIREDATE        SAL     COMM    DEPTNO
>     -----------------------------------------------------------------------
>     7839  KING       PRESIDENT      11/17/81 120000 5000            10
>     7698  BLAKE      MANAGER   7839 05/01/81 120000 2850            30
>     7782  CLARK      MANAGER   7839 06/09/81 120000 2450            10
>     7566  JONES      MANAGER   7839 04/02/81 120000 2975            20
>     7654  MARTIN     SALESMAN  7698 09/28/81 120000 1250    1400    30
>     7499  ALLEN      SALESMAN  7698 02/20/81 120000 1600    300     30
>     7844  TURNER     SALESMAN  7698 09/08/81 120000 1500    0       30
>     7900  JAMES      CLERK     7698 12/03/81 120000 950             30
>     7521  WARD       SALESMAN  7698 02/22/81 120000 1250    500     30
>     7902  FORD       ANALYST   7566 12/03/81 120000 3000            20
>     7369  SMITH      CLERK     7902 12/17/80 120000 800             20
>     7788  SCOTT      ANALYST   7566 12/09/82 120000 3000            20
>     7876  ADAMS      CLERK     7788 01/12/83 120000 1100            20
>     7934  MILLER     CLERK     7782 01/23/82 120000 1300            10

2. Display only those rows of the `emp` table in department 10, ordering the rows by salary in increasing order. I also request that a maximum of 20 characters be used to display string information.

   >     SQL> execute PLVdyn.disptab ('emp', 20, 'deptno = 10 order by sal');
   >     -----------------------------------------------------------------------
   >                                Contents of emp
   >     -----------------------------------------------------------------------
   >     EMPNO ENAME      JOB       MGR  HIREDATE        SAL     COMM    DEPTNO
   >     -----------------------------------------------------------------------
   >     7934  MILLER     CLERK     7782 01/23/82 120000 1300            10
   >     7782  CLARK      MANAGER   7839 06/09/81 120000 2450            10
   >     7839  KING       PRESIDENT      11/17/81 120000 5000            10

3. I use the `disptab` procedure inside the PLVlog package to display the contents of the log table when the user has requested logging to a database table. The PLVlog display procedure is shown below. If the log type is set to database table, `disptab` is called. Otherwise the PLVtab package is used to display the contents of the PL/SQL table containing the log.

   >     PROCEDURE display IS
   >     BEGIN
   >        IF log_type = PLV.dbtab
   >        THEN
   >           /* Use generic table display program? */
   >           PLVdyn.disptab (log_struc.table_name, 30);
   >        ELSIF log_type = PLV.pstab

   >        THEN
   >           PLVtab.display
   >              (log_pstable,

   >               pstab_count,
   >               'PL/Vision Log');
   >        END IF;
   >     END;

   Notice that the user does not have to provide any information about the structure of the table. The `disptab` procedure gets that information itself -- and this is precisely the aspect of `disptab` that makes it a Method 4 dynamic SQL example and very useful.

### 19.5.8 Controlling Execution of Dynamic SQL

You can never have enough flexibility. That's my motto. I built this package to execute my dynamic SQL and then I actually encountered circumstances in which I wanted to run all of my code, but preferred to not actually execute the dynamic SQL. So I added yet another toggle to the PLVdyn package that would give me just this capability.

To turn on execution of dynamic SQL with PLVdyn, you call the `execsql` procedure. Here is an example of calling `execsql` from within a SQL*Plus session:

>     SQL> exec PLVdyn.execsql

To turn off execute of dynamic SQL in PLVdyn, call `noexecsql` as shown below:

>     SQL> exec PLVdyn.noexecsql

The executing function returns TRUE if PLVdyn is currently executing the SQL statements passed to it. You can call this function; PLVdyn calls the function itself to decide if it should call DBMS_SQL.EXECUTE, as you can see in the implementation of `PLVdyn.execute`:

>     PROCEDURE execute (cur_in IN INTEGER) IS
>     BEGIN
>        IF executing
>        THEN
>           dml_feedback := DBMS_SQL.EXECUTE (cur_in);
>        ELSE
>           p.l ('PLVdyn: Execution disabled...');
>        END IF;
>     END;

### 19.5.9 Tracing Use of Dynamic SQL

With PLVdyn, you construct a SQL statement and pass that string to a PLVdyn program for parsing or execution. It's a big time saver, but it also implies a loss of some control. You trust PLVdyn to do the right thing -- and it does. The question remains, however: what is your code passing to PLVdyn?

The code used to construct the dynamic SQL statement is often complicated (you can see this in some of my high-level operators). As I began to use PLVdyn, I often found that I wanted to see the SQL statement that PLVdyn was executing. I needed to verify that my calling program had put the SQL together properly.

There were two ways I could display my SQL statement:

1. Put calls to DBMS_OUTPUT before each of my calls to PLVdyn modules. In this way, I do not have to change PLVdyn. (It is not, after all, the fault of PLVdyn -- or its author! -- that I wasn't sure what my code was doing.)

2. Put the trace inside PLVdyn. This approach would require changes to the base package. On the other hand, it would be done once and then be available for all users of PLVdyn.

To me, the second approach was clearly preferable. If I were going to put this trace inside the package, I needed to make sure that the trace displayed only my SQL statement when I wanted to see it. When the package was used in a production environment, I couldn't have extraneous messages showing up on the user's screen.

I therefore implemented my trace with the following procedures and functions:

>     PROCEDURE showsql (start_with_in IN VARCHAR2 := NULL);
>     PROCEDURE noshowsql;
>     FUNCTION showing RETURN BOOLEAN;

The `showsql` procedure turns on the trace. If you execute `PLVdyn.noshowsql`, you turn off the trace. The `showing` function tells you the current state of the toggle.

When you turn on the PLVdyn trace, you can specify a "start with" string. If this string is not NULL, then that serves as the starting point for display of the dynamic SQL. You can use that option to skip over portions of SQL in which you have no interest. This feature was more useful and necessary in the early days of the PLVdyn trace when I had not incorporated the PLVprs string-wrapping functionality.

#### 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 Executing PLVdyn in Different Schemas

When you execute stored code, you run it under the authority of the owner of that code. This has some startling implications for a package that frontends the DBMS_SQL package. Suppose that you install PLVdyn along with all of the other PL/Vision packages in a single account and then grant execute authority on PLVdyn to PUBLIC. This way all users can more easily take advantage of dynamic SQL in PL/SQL. Let's look at the implications of such an architecture.

Suppose that PL/Vision (and PLVdyn) is owned by the PLVISION account and that SCOTT is another account in the database instance. A DBA logs in to SCOTT to perform some database tuning. It seems that the company has added many employees over the years and the `emp` table now has 6,000,000 rows. An index is needed on the `ename` table to improve query performance.

So the DBA logs into the new Oracle Forms frontend she built to make her life easier. It uses PLVdyn to perform a variety of administrative tasks. Through a fill-in-the-form process, she ends up executing a CREATE INDEX command as follows:

>     PLVdyn.ddl ('create index empname_idx on emp(ename)');

So PLVdyn does its thing and the Oracle Forms application notifies the DBA that the job is done. The DBA is impressed at how rapidly the index was built and notifies the application development team that all is better now. Fifteen minutes of quiet pass for the DBA before she gets an angry call from a developer:

"The performance hasn't changed one bit!" he says angrily. "The screens still work just as slowly as before when I try to search for an employee by name."

#### 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.

19.4 PLVdyn: A Code Layer over DBMS_SQL |  | 19.6 PLVfk: Generic Foreign Key Lookups

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.