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.
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));
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.
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 .
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.
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 :
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.
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;
FUNCTION plsql_block (string_in IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN 'BEGIN ' || RTRIM (string_in, ';') || '; END;'; END;
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:
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.
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; /
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.
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:
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.
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).
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.
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:
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.
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.
PLVdyn offers two execute-related programs that are nothing more than passthroughs to the corresponding DBMS_SQL element. The headers for these passthroughs are:
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:
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:
Here are some examples of output from disptab :
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
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.
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:
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:
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.
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:
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.
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."
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)');
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.