[Chapter 19] 19.3 The Dynamic Packages of PL/Vision

19.3 The Dynamic Packages of PL/Vision

The builtin DBMS_SQL package supports all four methods of dynamic SQL , as well as dynamic PL/SQL code execution. It is a very powerful and useful tool that can in many ways transform the way in which you build applications, especially generic, reusable utilities, with PL/SQL .

There is, however, a tiny, little problem with DBMS_SQL: it is just too darn complicated. It consists of more than a dozen procedures and functions. These programs need to be used in a very particular sequence. Furthermore, depending on the method of dynamic SQL you wish to implement, you will use different combinations of those builtins. Finally, it just comes down to an awful lot of typing and know-how, even if you want to do something relatively simple. (See the sidebar for a general description of the flow of program calls for dynamic SQL . See Chapter 15, PLVvu: Viewing Source Code and Compile Errors , for a more complete description of DBMS_SQL.)

The result of this complexity is that relatively few developers take full advantage of all that DBMS_SQL has to offer. And since many of the actions required for dynamic SQL are the same regardless of the SQL statement, those individuals will be writing the same code over again.

What is wrong with this picture? Code redundancy is a maintenance nightmare. Requiring all developers to know the picayune details of technology like dynamic SQL is a productivity nightmare. Getting all of these versions of dynamic SQL to work is a code quality nightmare. Hey! Working with PL/SQL should not resemble a Freddy Krueger sequel. There's got to be something we can do here.

The builtin DBMS_SQL package allows you to dynamically construct and execute SQL and PL/SQL statements. You get full programmatic control -- and with it comes full responsibility . With DBMS_SQL, nothing is taken for granted. You must specify each and every operation on the SQL statement, usually with a wide variety of procedure calls, from the SQL statement itself down to the values of bind variables and the data types of columns in SELECT statements.

To execute dynamic SQL (and PL/SQL ) with the DBMS_SQL you must follow this general flow:

Open a cursor. When you open a cursor, you ask the RDBMs to set aside and maintain a valid cursor structure for your use with future DBMS_SQL calls. The RDBMs returns an INTEGER "handle" to this cursor. You will use this handle in all future calls to DBMS_SQL modules for this dynamic SQL statement. Note that this cursor is completely distinct from static PL/SQL cursors (whether implicit or explicit).

Parse the SQL statement. Before you can specify bind variable values and column structures for the SQL statement, it must be parsed by the RDBMs. This parse phase verifies that the SQL statement is properly constructed. It then associates the SQL statement with your cursor handle. Note that when you parse a DDL statement it is also executed immediately. Upon successful completion of the DDL parse, the RDBMs also issues an implicit commit. This behavior is consistent with that of SQL*Plus.

Bind all host variables. If the SQL statement contains references to host PL/SQL variables, you include placeholders to those variables in the SQL statement by prefacing their names with a colon, as in :salary . You must then bind the actual value for that variable into the SQL statement.

Define the columns in SELECT statements. Each column in the list of the SELECT must be defined. This define phase sets up a correspondence between the expressions in the list of the SQL statement and local PL/SQL variables that receive the values when a row is fetched (see COLUMN_VALUE). This step is necessary only for SELECT statements and is roughly equivalent to the INTO clause of an implicit SELECT statement in PL/SQL .

Execute the SQL statement. Execute the specified cursor, that is, its associated SQL statement. If the SQL statement is an INSERT, UPDATE, or DELETE, the EXECUTE command returns the numbers of rows processed. Otherwise you should ignore that return value.

Fetch rows from the dynamic SQL query. If you execute a SQL statement, you must then fetch the rows from the cursor, as you would with a PL/SQL cursor. When you fetch, however, you do not fetch directly into local PL/SQL variables.

Retrieve values from the execution of the dynamic SQL. If the SQL statement is a query, you will retrieve values from the SELECT expression list using COLUMN_VALUE. If you have executed a PL/SQL block, you will use VARIABLE_VALUE to retrieve any bind variables included in the PL/SQL code.

Close the cursor. Just as with normal PL/SQL cursors, you should always clean up by closing the cursor when you are done. This releases the memory associated with the cursor.

The answer is simple, at least in concept: build a package. And that is what I did. In fact, I built three packages for dynamic SQL that make it easier to use the builtin DBMS_SQL package:

PLVdyn: Gives a thorough layer of code built around the DBMS_SQL builtin package.

PLVdyn1: Supports single bind variable dynamic SQL .

PLVfk: Offers a generic utility to perform foreign key lookups for any table.

PLVdyn and PLVfk are covered in this chapter; PLVdyn1, which works in similar fashion to PLVdyn, is described on the companion disk.


19.2 Declarative Programming in PL/SQL		19.4 PLVdyn: A Code Layer over DBMS_SQL

19.3 The Dynamic Packages of PL/Vision

A DBMS_SQL Recap