Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 19.4 PLVdyn: A Code Layer over DBMS_SQLChapter 19
PLVdyn and PLVfk: Dynamic SQL and PL/SQL
Next: 19.6 PLVfk: Generic Foreign Key Lookups
 

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 PLVdyn.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 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.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
  1. 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
  2. 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.


Previous: 19.4 PLVdyn: A Code Layer over DBMS_SQLAdvanced Oracle PL/SQL Programming with PackagesNext: 19.6 PLVfk: Generic Foreign Key Lookups
19.4 PLVdyn: A Code Layer over DBMS_SQLBook Index19.6 PLVfk: Generic Foreign Key Lookups

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.