The dynamic SQL of DBMS_SQL allows you to create completely generic modules to manipulate objects in the Oracle7 Server. You can, for instance, write a procedure that drops the specified table, but you can also create a module that will drop whatever kind of object you specify, as shown in this first version of drop_object:
CREATE OR REPLACE PROCEDURE drop_object (type_in IN VARCHAR2, name_in IN VARCHAR2) IS /* Declare and create a cursor to use for the dynamic SQL */ cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk PLS_INTEGER; BEGIN /* Construct the SQL statement, parse it and execute it. */ DBMS_SQL.PARSE (cur, 'DROP ' || type_in || ' ' || name_in, DBMS_SQL.NATIVE); fdbk := DBMS_SQL.EXECUTE (cur); DBMS_SQL.CLOSE_CURSOR (cur); END; /
Well, that was straightforward enough. But how useful is it? Sure, it lets me execute DDL in PL/SQL, which wasn't possible before. But assuming that I have written this procedure as part of a broader interface to manage database objects from a screen, it is fairly limited. It is, in fact, simply equivalent to a DROP OBJECT statement. Boooring. Why not utilize the flexibility of the PL/SQL language to provide additional productivity, above and beyond the "straight" DDL? Wouldn't it be nice to, for example, drop all packages with names like "STR%" or drop all objects of any type in a schema with a single command?
To implement these kinds of requests, I need to let the user pass in wildcarded object names and types. I can then use these values to identify N number of matching objects. Where are these objects defined? In the USER_OBJECTS or ALL_OBJECTS data dictionary view. Interestingly, then, in my final version of drop_object, I combine the use of both static and dynamic SQL to add value to the standard DROP OBJECT command:
/* Filename on companion disk: dropobj.sp */* CREATE OR REPLACE PROCEDURE drop_object (type_in IN VARCHAR2, name_in IN VARCHAR2) IS /* The static cursor retrieving all matching objects */ CURSOR obj_cur IS SELECT object_name, object_type FROM user_objects WHERE object_name LIKE UPPER (name_in) AND object_type LIKE UPPER (type_in) ORDER BY object_name; cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk PLS_INTEGER; BEGIN /* For each matching object ... */ FOR obj_rec IN obj_cur LOOP /* Reusing same cursor, parse and execute the drop statement. */ DBMS_SQL.PARSE (cur, 'DROP ' || obj_rec.object_type || ' ' || obj_rec.object_name, DBMS_SQL.NATIVE); fdbk := DBMS_SQL.EXECUTE (cur); END LOOP; DBMS_SQL.CLOSE_CURSOR (cur); END; /
Using this enhanced utility, I can now remove all objects in my schema that are used in the Order Entry system with this single command:
SQL> exec drop_object ('%', 'oe%');
Or I could be more selective and simply drop all tables containing the substring "emp."
SQL> exec drop_object ('table', '%emp%');
The drop_object procedure demonstrates the flexiblity and power that PL/SQL can bring to administrative tasks. It also clearly points out the inherent dangers of this power. With drop_object in place, you can remove everything in your schema with one program! Use this procedure -- and any other similar utilities you build -- with the utmost of care.
I am always looking for ways to cut down on the code I need to write to maintain foreign keys in Oracle-based applications. For all its great features, Oracle Forms -- just like its predecessor, SQL*Forms V3 -- does not offer comprehensive support for handling foreign keys. Sure, there is the LOV object and its associated record group, which can be used to look up and validate entries on the screen. This object cannot, however, be used to perform Post-Query lookups of a foreign key's description -- a ubiquitous operation in an application built on a normalized database.
This same lookup process could also take place in a report, in a graph, in an embedded C program, and so on. Before the advent of DBMS_SQL, the only solution was to build a function for each separate entity that serves as a foreign key in a table. This function would take the foreign key and return the name or description. The specifications for such functions would look like these,
FUNCTION caller_name (caller_id IN caller.caller_id%TYPE) RETURN VARCHAR2; FUNCTION company_name (company_id IN company.company_id%TYPE) RETURN VARCHAR2; FUNCTION company_type (company_type_cd IN company.company_type_cd%TYPE) RETURN VARCHAR2;
and so on, for as many foreign keys as you've got. And every time a new foreign key is added to the mix, you must write a new function.
Wouldn't it be just fabulous if you could construct a single generic function using dynamic SQL that would work for all foreign keys? Let's give it a shot.
First, what information would I need to pass to this function in order to construct the SQL statement to retrieve the name or description? Here are some possibilities:
These parameters would allow me to construct SQL statements that look like the following:
SELECT caller_nm FROM caller WHERE caller_id = 154 SELECT call_type_nm FROM call_type WHERE call_type_id = 2
The steps I need to perform are straightforward:
My first version of this function, fk_name, shows a generic function that returns the name and description of a foreign key.
/* Filename on companion disk: fkname.sf */* CREATE OR REPLACE FUNCTION fk_name (fk_id_in IN INTEGER, fk_table_in IN VARCHAR2, fk_id_col_in IN VARCHAR2, fk_nm_col_in IN VARCHAR2) RETURN VARCHAR2 IS /* Declare and obtain a pointer to a cursor */ cur INTEGER := DBMS_SQL.OPEN_CURSOR; /* Variable to receive feedback from package functions */ fdbk INTEGER; /* || The return value of the function. Notice that I have || to hardcode a size in my declaration. */ return_value VARCHAR2(100) := NULL; BEGIN /* || Parse the query. I construct most of the SQL statement from || the parameters with concatenation. I also include a single || bind variable for the actual foreign key value. */ DBMS_SQL.PARSE (cur, 'SELECT ' || fk_nm_col_in || ' FROM ' || fk_table_in || ' WHERE ' || fk_id_col_in || ' = :fk_value', DBMS_SQL.NATIVE); /* Bind the variable with a specific value -- the parameter */ DBMS_SQL.BIND_VARIABLE (cur, 'fk_value', fk_id_in); /* Define the column in the cursor for the FK name */ DBMS_SQL.DEFINE_COLUMN (cur, 1, fk_nm_col_in, 100); /* Execute the cursor, ignoring the feedback */ fdbk := DBMS_SQL.EXECUTE (cur); /* Fetch the row. If feedback is 0, no match found */ fdbk := DBMS_SQL.FETCH_ROWS (cur); IF fdbk > 0 THEN /* Found a match. Extract the value/name for the key */ DBMS_SQL.COLUMN_VALUE (cur, 1, return_value); END IF; /* || Close the cursor and return the description, which || could be NULL if no records were fetched. */ DBMS_SQL.CLOSE_CURSOR (cur); RETURN return_value; END; /
I can now use this function in a Post-Query trigger in Oracle Forms, as follows:
:call.name := fk_name (:call.caller_id, 'caller', 'caller_id', 'caller_nm'); :call.call_type_ds := fk_name (:call.call_type_id, 'call_type', 'call_type_id', 'call_type_nm');
Well, that was fun. I now have a generic look-up for foreign keys. Instead of stopping at this point, however, let's explore some ways to improve the functionality and ease of use of this function. Several things caught my eye on the first pass:
Can I change fk_name to handle some of these concerns? I can certainly add a parameter with a maximum name length to use in the call to COLUMN_VALUE. I can support VARCHAR2 datatypes for foreign keys by placing the function inside a package and overloading the definition of the function. What about all those parameters and the naming conventions? Ideally, I would like to allow a developer to call fk_name with no more information than in the following examples:
:call.name := fk_name (:call.caller_id, 'caller'); :call.call_type_ds := fk_name (:call.call_type_id, 'call_type');
In this scenario, the function would use the name of the table to generate the names of the key and name columns and stuff them into the SQL statement. Sounds reasonable to me. The second version of fk_name supports default names for these two columns.
In this version, the last three parameters have default values. If the user does not specify an ID column name, the default is the table name with the default suffix. The same goes for the fk_name column. If the user includes a value for either of these arguments, then if the value starts with an underscore ( _ ), it will be used as a suffix to the table name. Otherwise, the value will be used as a complete column name. The following table shows how parameter values will be converted inside the program.
Here, then, in Version 2, we have an even more generic function to return foreign key names.
/* Filename on companion disk: fkname2.sf */* CREATE OR REPLACE FUNCTION fk_name (fk_id_in IN INTEGER, fk_table_in IN VARCHAR2, fk_id_col_in IN VARCHAR2 := '_ID', fk_nm_col_in IN VARCHAR2 := '_NM', max_length_in IN INTEGER := 100) RETURN VARCHAR2 /* I will not repeat any comments from first version of fk_name. */ IS /* || Local variables to hold column names, since I must construct || those names based on the values provided. If the column names || are NULL, then fall back on the defaults. */ fk_id_column VARCHAR2(60) := NVL (fk_id_col_in, '_ID'); fk_nm_column VARCHAR2(60) := NVL (fk_nm_col_in, '_NM'); cur INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk INTEGER; /* || The return value of the function. Notice that even though one || of the parameters now specifies a maximum size for the return || value, I still do have to hardcode a size in my declaration. */ return_value VARCHAR2(100) := NULL; /*---------------------- Local Module ---------------------------*/ PROCEDURE convert_column (col_name_inout IN OUT VARCHAR2) /* || Construct the column name. If the argument begins with a "_", || use as suffix to table name. Otherwise, substitute completely. */ IS BEGIN IF SUBSTR (col_name_inout, 1, 1) = '_' THEN col_name_inout := fk_table_in || col_name_inout; ELSE /* Default value on variable declaration already handles it */ NULL; END IF; END; BEGIN /* Convert the column names as necessary based on arguments */ convert_column (fk_id_column); convert_column (fk_nm_column); /* Parse statement using converted column names */ DBMS_SQL.PARSE (cur, 'SELECT ' || fk_nm_column || ' FROM ' || fk_table_in || ' WHERE ' || fk_id_column || ' = :fk_value', DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE (cur, 'fk_value', fk_id_in); DBMS_SQL.DEFINE_COLUMN (cur, 1, fk_nm_column, max_length_in); fdbk := DBMS_SQL.EXECUTE (cur); fdbk := DBMS_SQL.FETCH_ROWS (cur); IF fdbk > 0 THEN DBMS_SQL.COLUMN_VALUE (cur, 1, return_value); END IF; DBMS_SQL.CLOSE_CURSOR (cur); RETURN return_value; END;
With this new version of fk_name, I can certainly retrieve the caller's name and the call type description without specifying all the columns, assuming that their columns match my conventions.
Of course, conventions do not hold so consistently in the real world. In fact, I have found that database administrators and data analysts will often treat an entity like caller, with its caller ID number and caller name, differently from the way they would treat a caller type, with its type code and description. The columns for the caller type table are more likely to be caller_typ_cd and caller_typ_ds. Fortunately, fk_name will still handle this situation as follows:
You might scoff and say, "Why bother providing just the suffixes? Might as well go ahead and provide the full column names." But there is a value to this approach: if the data analysts have adopted standards for their naming conventions of tables and key columns, the fk_name interface supports and reinforces these standards, and avoids supplying redundant information.
Is that it, then? Have we gone as far as we can go with fk_name? Surely some of you have looked at those rather simple SELECT statements and thought, "Gee, very few of my lookups actually resemble such queries." I agree. Sometimes you will need to check an additional column on the table, such as a "row active?" flag. You might even have several records, all for the same primary key, but active for different periods. So you should also pass a date against which to check.
How can you handle these application-specific situations? When in doubt, just add another parameter!
Sure. Why not add a parameter containing either a substitute WHERE clause for the SQL statement, or a clause to be appended to the rest of the default WHERE clause? The specification for fk_name would then change to the following:
The rule for this WHERE clause would be as follows: if the string starts with the keywords AND or OR, then the text is appended to the default WHERE clause. Otherwise, the argument substitutes completely for the default WHERE clause.
Rather than repeat the entire body of fk_name, I offer only the modifications necessary to PARSE, and thus effect this change in the following code:
IF UPPER (where_clause_in) LIKE 'AND%' OR UPPER (where_clause_in) LIKE 'OR%' THEN /* Append the additional Boolean expressions to default */ where_clause := ' WHERE ' || fk_id_column || ' = :fk_value ' || where_clause_in; ELSIF where_clause_in IS NOT NULL THEN /* Substitute completely the WHERE clause */ where_clause := ' WHERE ' || where_clause_in; ELSE /* Just stick with default */ where_clause := ' WHERE ' || fk_id_column || ' = :fk_value'; END IF; /* Now the call to PARSE uses the pre-processed WHERE clause */ DBMS_SQL.PARSE (cur, 'SELECT ' || fk_nm_column || ' FROM ' || fk_table_in || where_clause, DBMS_SQL.NATIVE);
Using this final version of fk_name, I can perform lookups as follows:
The DESCRIBE_COLUMNS procedure provides a critical feature for those of us writing generic, flexible code based on dynamic SQL. With earlier versions of DBMS_SQL, there was no way to query runtime memory to find out the internal structure of a cursor. Now you can do this with DESCRIBE_COLUMNS, but it is very cumbersome. As shown in the section Section 2.3.11, "Describing Cursor Columns " ," you must declare a PL/SQL table, read the cursor structure into that table, and then traverse the table to get the information you need.
A much better approach is to write the code to perform these steps once and then encapsulate all that knowledge into a package. Then you can simply call the programs in the package and not have to worry about all the internal data structures and operations that have to be performed.
/* Filename on companion disk: desccols.spp */* CREATE OR REPLACE PACKAGE desccols IS varchar2_type CONSTANT PLS_INTEGER := 1; number_type CONSTANT PLS_INTEGER := 2; date_type CONSTANT PLS_INTEGER := 12; char_type CONSTANT PLS_INTEGER := 96; long_type CONSTANT PLS_INTEGER := 8; rowid_type CONSTANT PLS_INTEGER := 11; raw_type CONSTANT PLS_INTEGER := 23; mlslabel_type CONSTANT PLS_INTEGER := 106; clob_type CONSTANT PLS_INTEGER := 112; blob_type CONSTANT PLS_INTEGER := 113; bfile_type CONSTANT PLS_INTEGER := 114; PROCEDURE forcur (cur IN INTEGER); PROCEDURE show (fst IN INTEGER := 1, lst IN INTEGER := NULL); FUNCTION numcols RETURN INTEGER; FUNCTION nthcol (num IN INTEGER) RETURN DBMS_SQL.DESC_REC; END desccols; /
Before we look at the implementation of this package, let's explore how you might use it. I declare a set of constants that give names to the various column types. This way, you don't have to remember or place in your code the literal values. Now notice that there are no other data structures defined in the specification. Most importantly, there is no declaration of a PL/SQL table based on DBMS_SQL. DESC_T to hold the description information. That table is instead hidden away inside the package body. You call the desccols. forcur procedure to "describe the columns for a cursor," passing it your cursor ID or handle, to load up that table by calling DESCRIBE_COLUMNS. You then can take any of the following actions against that PL/SQL table of column data:
The following script defines a cursor, extracts the cursor information with a call to desccols.forcur, and then shows the cursor information:
/* Filename on companion disk: desccols.tst */* DECLARE cur INTEGER := DBMS_SQL.OPEN_CURSOR; BEGIN DBMS_SQL.PARSE (cur, 'SELECT ename, sal, hiredate FROM emp', DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN (cur, 1, 'a', 60); DBMS_SQL.DEFINE_COLUMN (cur, 1, 1); DBMS_SQL.DEFINE_COLUMN (cur, 1, SYSDATE); desccols.forcur (cur); desccols.show; DBMS_SQL.CLOSE_CURSOR (cur); END; /
Here is the output in SQL*Plus:
Column 1 ENAME 1 Column 2 SAL 2 Column 3 HIREDATE 12
In this next example, I load up the column information, use the nthcol function to get the information about just one column, deposit it in a locally declared record, and then check the column type.
DECLARE cur integer := dbms_sql.open_cursor; rec DBMS_SQL.DESC_REC; BEGIN dbms_sql.PARSE (cur, 'SELECT ename, sal, hiredate FROM emp', DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN (cur, 1, 'a', 60); DBMS_SQL.DEFINE_COLUMN (cur, 1, 1); DBMS_SQL.DEFINE_COLUMN (cur, 1, SYSDATE); desccols.forcur (cur); rec := desccols.nthcol (1); IF rec.col_type = desccols.varchar2_type THEN DBMS_OUTPUT.PUT_LINE ('Process as string!'); END IF; DBMS_SQL.CLOSE_CURSOR (cur); END; /
And I get this output when executed:
Process as string!
Notice how I have shifted from dealing with the low-level details of the DESCRIBE_COLUMNS built-in to manipulating all that data through a clear, easy-to-use API. It is not as though the code you need to write (and you will find in the body of the next package) is all that complicated. But why bother with this code again and again when you can write it once and then just pass in the pointer to the cursor and let the package do all the work?
The implementation of the desccols package is straightforward.
/* Filename on companion disk: desccols.spp */* CREATE OR REPLACE PACKAGE BODY desccols IS /* Here is the PL/SQL table holding the column information. */ desctab DBMS_SQL.DESC_TAB; desccnt PLS_INTEGER; firstrow PLS_INTEGER; lastrow PLS_INTEGER; PROCEDURE forcur (cur IN INTEGER) IS BEGIN /* Clear out the PL/SQL table */ desctab.DELETE; /* Fill up the PL/SQL table */ DBMS_SQL.DESCRIBE_COLUMNS (cur, desccnt, desctab); /* Get the first and last row numbers to avoid future lookups */ firstrow := desctab.FIRST; lastrow := desctab.LAST; END; PROCEDURE show (fst IN INTEGER := 1, lst IN INTEGER := NULL) IS BEGIN IF desccnt > 0 THEN /* Show the specified rows. */ FOR colind IN GREATEST (fst, firstrow) .. LEAST (NVL (lst, lastrow), lastrow) LOOP /* Add additional lines of output as you desire */ DBMS_OUTPUT.PUT_LINE ('Column ' || TO_CHAR (colind)); DBMS_OUTPUT.PUT_LINE (desctab(colind).col_name); DBMS_OUTPUT.PUT_LINE (desctab(colind).col_type); END LOOP; END IF; END; FUNCTION numcols RETURN INTEGER IS BEGIN RETURN desccnt; END; FUNCTION nthcol (num IN INTEGER) RETURN DBMS_SQL.DESC_REC IS retval DBMS_SQL.DESC_REC; BEGIN /* If a valid row number, retrieve that entire record. */ IF num BETWEEN firstrow AND lastrow THEN retval := desctab(num); END IF; RETURN retval; END; END; /
This section examines the kind of code you need to write to perform dynamic SQL Method 4. Method 4, introduced early in this chapter, supports queries that have a variable (defined only at runtime) number of items in the SELECT list and/or a variable number of host variables. Here is an example of Method 4 dynamic SQL:
'SELECT ' || variable_select_list || ' FROM ' || table_name || ' WHERE sal > :minsal ' AND ' || second_clause || order_by_clause
Notice that with this SQL statement, I do not know how many columns or expressions are returned by the query. The names of individual columns are "hidden" in the variable select list. I also do not know the full contents of the WHERE clause; the minsal bind variable is obvious, but what other bind variable references might I find in the second_clause string? As a result of this uncertainty, Method 4 dynamic SQL is the most complicated kind of dynamic query to handle with DBMS_SQL.
What's so hard about that? Well, if I am going to use the DBMS_SQL package to execute and fetch from such a query, I need to write and compile a PL/SQL program. Specifically, to parse the SQL statement, I need to define the columns in the cursor with calls to DEFINE_COLUMN -- yet I do not know the list of columns at the time I am writing my code. To execute the query, I must associate values to all of my bind variables (identifiers with a ":" in front of them) by calling BIND_VARIABLE -- yet I do not know the names of those bind variables at the time I write my code. Finally, to retrieve data from the result set of the query I also need to call COLUMN_VALUE for each column. But, again, I do not know the names or datatypes of those columns up front.
Sounds challenging, doesn't it? In fact, working with these incredibly dynamic SQL strings requires some interesting string parsing and some even more creative thinking.
When would you run into Method 4? It arises when you build a frontend to support ad-hoc query generation by users, or when you want to build a generic report program, which constructs the report format and contents dynamically at runtime. I also encountered it recently when I decided to build a PL/SQL procedure to display the contents of a table -- any table, as specified by the user at runtime. This section explores what it took for me to implement this "in table" procedure.
Before I dive into the PL/SQL required to create my procedure, I should explore my options. After all, it is certainly very easy for me to build a script in SQL*Plus to display the contents of any table.
SELECT * FROM &1
I could even spice it up with a variable select list and WHERE clause as follows:
SELECT &1 FROM &2 WHERE &3 ORDER BY &4
In fact, SQL*Plus is a very flexible, powerful front-end tool for SQL scripts. Yet no matter how fancy I get with substitution parameters in SQL*Plus, this is not code I can run from within PL/SQL. Furthermore, PL/SQL gives me more procedural control over how to specify the data I want to see and how to display the data. Finally, if I use PL/SQL, then I get to play with DBMS_SQL! On the downside, however, from within PL/SQL I must rely on DBMS_OUTPUT (described in Chapter 7, Defining an Application Profile ) to display my table contents, so I must reckon with the buffer limitations of that built-in package (a maximum of 1,000,000 bytes of data -- you will clearly not use my procedure to display very large quantities of data).
So I will use PL/SQL and DBMS_SQL. But before building any code, I need to come up with a specification. How will the procedure be called? What information do I need from my user (a developer, in this case)? What should a user have to type to retrieve the desired output? I want my procedure (which I call "intab" for "in table") to accept the inputs in the following table.
Given these inputs, the specification for my procedure becomes the following:
PROCEDURE intab (table_in IN VARCHAR2, string_length_in IN INTEGER := 20, where_in IN VARCHAR2 := NULL, date_format_in IN VARCHAR2 := 'MM/DD/YY HHMISS')
Here are some examples of calls to intab:
execute intab ('emp'); execute intab ('emp', 20, 'deptno = ' || v_deptno || ' order by sal');
These two calls to intab produce the following output:
execute intab ('emp'); ----------------------------------------------------------------------------- Contents of emp ----------------------------------------------------------------------------- EMPNO ENAMEJOBMGR HIREDATESALCOMMDEPTNO ----------------------------------------------------------------------------- 7839 KINGPRESIDENT11/17/81 120000500010 7698 BLAKE MANAGER7839 05/01/81 120000285030 7782 CLARKMANAGER7839 06/09/81 120000245010 7566 JONESMANAGER7839 04/02/81 120000297520 7654 MARTINSALESMAN7698 09/28/81 1200001250 140030 7499 ALLENSALESMAN7698 02/20/81 1200001600 3000 7844 TURNERSALESMAN7698 09/08/81 1200001500 0 30 7900 JAMESCLERK7698 12/03/81 12000095030 7521 WARDSALESMAN7698 02/22/81 1200001250 500 30 7902 FORDANALYST7566 12/03/81 120000300020 7369 SMITHCLERK7902 12/17/80 12000080020 7788 SCOTTANALYST7566 12/09/82 120000300020 7876 ADAMSCLERK7788 01/12/83 120000110020 7934 MILLERCLERK7782 01/23/82 120000130010 execute intab ('emp', 20, 'deptno = 10 order by sal'); ----------------------------------------------------------------------- Contents of emp ----------------------------------------------------------------------- EMPNO ENAMEJOB MGR HIREDATESALCOMMDEPTNO ----------------------------------------------------------------------- 7934 MILLERCLERK7782 01/23/82 120000130010 7782 CLARKMANAGER7839 06/09/81 120000245010 7839 KINGPRESIDENT11/17/81 120000500010
Notice that the user does not have to provide any information about the structure of the table. My program will get that information itself -- precisely the aspect of intab that makes it a Method 4 dynamic SQL example.
While this version of intab will certainly be useful, I am the first to recognize that there are many other possible enhancements to intab, including:
So, yes, there is always more one can do, but this one (yours truly) would like to leave some interesting work for his readers. To encourage you to take my intab and "run with it," I will, in this section, step you through the usage of DBMS_SQL required to implement the intab procedure. (The full program is contained on the companion disk.)
In order to display the contents of a table, follow these steps:
In order to extract the data from the table, I have to construct the S ELECT statement. The structure of the query is determined by the various inputs to the procedure (table name, WHERE clause, etc.) and the contents of the data dictionary. Remember that the user does not have to provide a list of columns. Instead, I must identify and extract the list of columns for that table from a data dictionary view. I have decided to use all_tab_columns in intab so the user can view the contents not only of tables he, or she, owns (which are accessible in user_tab_columns), but also any table for which he, or she, has SELECT access.
Here is the cursor I use to fetch information about the table's columns:
CURSOR col_cur (owner_in IN VARCHAR2, table_in IN VARCHAR2) IS SELECT column_name, data_type, data_length, data_precision, data_scale FROM all_tab_columns WHERE owner = owner_in AND table_name = table_in;
With this column cursor, I extract the name, datatype, and length information for each column in the table. How should I store all of this information in my PL/SQL program? To answer this question, I need to think about how that data will be used. It turns out that I will use it in many ways, for example:
Therefore, I need to work with the column information several times throughout my program, yet I do not want to read repeatedly from the data dictionary. As a result, when I query them out of the all_tab_columns view, I will store the column data in three PL/SQL tables.
So if the third column of the emp table is SAL, then colname(3) = `SAL', coltype(3) = `NUMBER', and collen(3) = 7, and so forth.
The name and datatype information is stored directly from the data dictionary. When I work with the DBMS_SQL built-ins, however, they do not use the strings describing the datatypes (such as "CHAR" and "DATE"). Instead, DEFINE_COLUMN and COLUMN_VALUE rely on PL/SQL variables to infer the correct datatypes. So I use three local functions, is_string, is_date, and is_number, to help me translate a datatype into the correct variable usage. The is_string function, for example, validates that both CHAR and VARCHAR2 are string datatypes:
FUNCTION is_string (row_in IN INTEGER) RETURN BOOLEAN IS BEGIN RETURN (coltype(row_in) IN ('CHAR', 'VARCHAR2')); END;
Figuring out the appropriate number of characters required to fit the column's data (the contents of the collen PL/SQL table) is a bit more complicated.
I need to take several different aspects of the column into account:
As you can see, the type of data partially determines the type of calculation I perform for the length. Here's the formula for computing a string column's length:
GREATEST (LEAST (col_rec.data_length, string_length_in), LENGTH (col_rec.column_name))
The formula for a numeric column length is as follows:
GREATEST (NVL (col_rec.data_precision, col_rec.data_length), LENGTH (col_rec.column_name))
Finally, here's the formula for a date column length:
GREATEST (LENGTH (date_format_in), LENGTH (col_rec.column_name))
I use these formulas inside a cursor FOR loop that sweeps through all the columns for a table (as defined in all_tab_columns). This loop (shown following) fills my PL/SQL tables:
FOR col_rec IN col_cur (owner_nm, table_nm) LOOP /* Construct select list for query. */ col_list := col_list || ', ' || col_rec.column_name; /* Save datatype and length for calls to DEFINE_COLUMN. */ col_count := col_count + 1; colname (col_count) := col_rec.column_name; coltype (col_count) := col_rec.data_type; /* Compute the column length with the above formulas in a local module. */ collen (col_count) := column_lengths; /* Store length and keep running total. */ line_length := line_length + v_length + 1; /* Construct column header line. */ col_header := col_header || ' ' || RPAD (col_rec.column_name, v_length); END LOOP;
When this loop completes, I have constructed the select list, populated my PL/SQL tables with the column information I need for calls to DEFINE_COLUMN and COLUMN_VALUE, and also created the column header line. Now that was a busy loop!
Next step? Construct the WHERE clause. In the following code, I check to see if the "WHERE clause" might actually just be a GROUP BY or ORDER BY clause. In those cases, I'll skip the WHERE part and attach this other information.
IF where_clause IS NOT NULL THEN IF (where_clause NOT LIKE 'GROUP BY%' AND where_clause NOT LIKE 'ORDER BY%') THEN where_clause := 'WHERE ' || LTRIM (where_clause, 'WHERE'); END IF; END IF;
I have now finished construction of the SELECT statement. Time to parse it, and then construct the various columns in the dynamic cursor object.
DBMS_SQL.PARSE (cur, 'SELECT ' || col_list || ' FROM ' || table_in || ' ' || where_clause, DBMS_SQL.NATIVE);
Of course, I want to go far beyond parsing. I want to execute this cursor. Before I do that, however, I must give some structure to the cursor. Remember: when you open a cursor, you have merely retrieved a handle to a chunk of memory. When you parse the SQL statement, you have associated a SQL statement with that memory. But as a next step, you must define the columns in the cursor so that it can actually store fetched data.
With Method 4 dynamic SQL, this association process is complicated. I cannot "hard-code" the number or type of calls to DEFINE_COLUMN in my program; I do not have all the information until runtime. Fortunately, in the case of intab, I have kept track of each column to be retrieved. Now all I need to do is issue a call to DEFINE_COLUMN for each row defined in my PL/SQL table colname. Before we go through the actual code, here are some reminders about DEFINE_COLUMN.
The header for this built-in procedure is as follows:
PROCEDURE DBMS_SQL.DEFINE_COLUMN (cursor_handle IN INTEGER, position IN INTEGER, datatype_in IN DATE|NUMBER|VARCHAR2)
There are three things to keep in mind with this built-in:
In the context of intab, the row in the PL/SQL table is the Nth position in the column list. The datatype is stored in the coltype PL/SQL table, but must be converted into a call to DEFINE_COLUMN using the appropriate local variable. These complexities are handled in the following FOR loop:
FOR col_ind IN 1 .. col_count LOOP IF is_string (col_ind) THEN DBMS_SQL.DEFINE_COLUMN (cur, col_ind, string_value, collen (col_ind)); ELSIF is_number (col_ind) THEN DBMS_SQL.DEFINE_COLUMN (cur, col_ind, number_value); ELSIF is_date (col_ind) THEN DBMS_SQL.DEFINE_COLUMN (cur, col_ind, date_value); END IF; END LOOP;
When this loop is completed, I will have called DEFINE_COLUMN for each column defined in the PL/SQL tables. (In my version this is all columns for a table. In your enhanced version, it might be just a subset of all these columns.) I can then execute the cursor and start fetching rows. The execution phase is no different for Method 4 than it is for any of the other simpler methods,
fdbk := DBMS_SQL.EXECUTE (cur);
where fdbk is the feedback returned by the call to EXECUTE. Now for the finale: retrieval of data and formatting for display.
I use a cursor FOR loop to retrieve each row of data identified by my dynamic cursor. If I am on the first row, I will display a header (this way, I avoid displaying the header for a query which retrieves no data). For each row retrieved, I build the line and then display it:
LOOP fdbk := DBMS_SQL.FETCH_ROWS (cur); EXIT WHEN fdbk = 0; IF DBMS_SQL.LAST_ROW_COUNT = 1 THEN /* We will display the header information here */ ... END IF; /* Construct the line of text from column information here */ ... DBMS_OUTPUT.PUT_LINE (col_line); END LOOP;
The line-building program is actually a numeric FOR loop in which I issue my calls to COLUMN_VALUE. I call this built-in for each column in the table (information that is stored in -- you guessed it -- my PL/SQL tables). As you can see below, I use my is_* functions to determine the datatype of the column and therefore the appropriate variable to receive the value.
Once I have converted my value to a string (necessary for dates and numbers), I pad it on the right with the appropriate number of blanks (stored in the collen PL/SQL table) so that it lines up with the column headers.
col_line := NULL; FOR col_ind IN 1 .. col_count LOOP IF is_string (col_ind) THEN DBMS_SQL.COLUMN_VALUE (cur, col_ind, string_value); ELSIF is_number (col_ind) THEN DBMS_SQL.COLUMN_VALUE (cur, col_ind, number_value); string_value := TO_CHAR (number_value); ELSIF is_date (col_ind) THEN DBMS_SQL.COLUMN_VALUE (cur, col_ind, date_value); string_value := TO_CHAR (date_value, date_format_in); END IF; /* Space out the value on the line under the column headers. */ col_line := col_line || ' ' || RPAD (NVL (string_value, ' '), collen (col_ind)); END LOOP;
There you have it. A very generic procedure for displaying the contents of a database table from within a PL/SQL program. It all fell pretty smoothly into place once I got the idea of storing my column structures in a set of PL/SQL tables. Drawn on repeatedly, those in-memory tables made it easy to implement Method 4 dynamic SQL -- another example of how taking full advantage of everything PL/SQL has to offer strengthens your ability to implement quickly and cleanly.
I suppose you could read this section and shrug, thinking: "Well, I don't feel like I have the time to write these kinds of generic utilities." I urge you to reject this line of reasoning. You always have time to create a more generic, generally useful implementation. Any time you spend up-front to craft high-quality, well-designed modules will pay off -- for you personally, and for all others who benefit from your labors.
PL/SQL offers endless possibilities for building reusable code, which will save you many hours of development, debugging, and maintenance. The DBMS_SQL package in particular is a veritable gold mine. Dive in and try your hand at dynamic SQL!
I cut my teeth, so to speak, with Oracle software on a product called SQL*Forms. Back around 1987-1989, it was a really hot product for application development, and I was a guru. I wrote recursive triggers. I wrote a debugger for this product so thoroughly lacking in a debugger. I could produce robust forms in little or no time. It was very sad, then, for many of us, when Windows and other GUIs came along, and Oracle Corporation did not keep up. Of course, Oracle Forms has come a long way and is competing well with PowerBuilder and SQL*Windows and Visual Basic. That makes me happy because it has some really neat features, one of which is indirect referencing.
Oracle Forms offers two built-ins, the NAME_IN function and the COPY procedure, which allow you to retrieve and set by name the values of variables inside your form. For example, I can set the value of the global variable GLOBAL.right_now to SYSDATE with the following call,
COPY (TO_CHAR (SYSDATE), 'global.right_now');
and I can retrieve the value of that same global with a call to NAME_IN:
v_date := TO_DATE (NAME_IN ('global.right_now'));
Some of you might, and should, be saying to yourself, "Well, heck, why not just use the assignment operator," as in,
v_date := TO_DATE (:GLOBAL.right_now);
and you would of course be right -- in this situation. The power of NAME_IN and COPY, however, become clear when instead of using them with "hard-coded" variable names, you construct the name of the variable at runtime. Here is an example:
COPY (TO_CHAR (SYSDATE), 'global.date_in_year' || TO_CHAR (v_yearnum));
In this case, the name of the variable to which SYSDATE is assigned is not determined until this line of code is executed and the v_yearnum variable is evaluated. This "indirect reference" to the GLOBAL variable in question offers developers a tremendous amount of flexibility. Indirect referencing will come in most handy when you are building generic utilities in which the very structure of your PL/SQL in-memory data is not defined until runtime.
I encountered the need for indirect referencing when building a PL/SQL code generator (now a product called PL/Generator from RevealNet). I wanted to provide a utility that would read a template file and then generate PL/SQL packages from that file. I found that by using indirect referencing, I could make these templates user-extensible. While this is neither the time nor place to really explore the generator technology, the following text shows a fragment of the template file. The square brackets ([ and ]) are used to "tag" template commands. The [IF] command implements "meta-conditional" logic, and that is where my indirect referencing comes into play:
[IF]myvars.checkstatus IF SQL%ROWCOUNT = 0 THEN DBMS_OUTPUT.PUT_LINE ('No records updated!'); END IF; [ENDIF]
That is the code to be processed by PL/Generator. So before I do the generation, I will set the myvars.checkstatus packaged global variable to "T" with a statement like this:
SQL> exec myvars.checkstatus := 'T';
When I run my utility, I employ indirect referencing to "look up" the value of the variable with the name "myvars.checkstatus." That lookup will return TRUE and the check against ROWCOUNT thus will be included in the package that is generated. To accomplish this feat, follow these steps:
In this section, I will show you how to build a package called dynvar to perform this kind of dynamic or indirect referencing for string values. You can easily overload the programs in dynvar to support other datatypes. Here is the specification of the package:
These three programs function as follows:
Here is a demonstration script using all three of these programs:
/* Filename on companion disk: dynvar.tst */* CREATE OR REPLACE PACKAGE TSTVAR IS str1 varchar2(2000); str2 varchar2(2000); END; / DECLARE v_pkg CHAR(7) := 'tstvar.'; localstr VARCHAR2(100); BEGIN dynvar.assign ('abc' || 'def' , localstr); /* Note 1 below */ DBMS_OUTPUT.PUT_LINE ('local string set to ' || localstr); dynvar.copyto ('abcdefghi', v_pkg || 'str1'); /* Note 2 below */ DBMS_OUTPUT.PUT_LINE ('global string set to ' || tstvar.str1); DBMS_OUTPUT.PUT_LINE ('value retrieved dynamically ' || dynvar.val (v_pkg || 'str1')); /* Note 3 below */ tstvar.str2 := 'tstvar.str1'; DBMS_OUTPUT.PUT_LINE ('double indirection gets us ' || dynvar.val (dynvar.val (v_pkg || 'str2'))); /* Note 4 below */ DBMS_OUTPUT.PUT_LINE ('expression retrieved dynamically ' || dynvar.val (v_pkg || 'str1' || '|| '' wow!''')); /* Note 5 below */ END; /
This is the output from the program:
SQL> @dynvar.tst Package created. local string set to abcdef global string set to abcdefghi value retrieved dynamically abcdefghi double indirection gets us abcdefghi expression retrieved dynamically abcdefghi wow!
Here are some notes on program behavior:
The following sections show the implementations of the dynvar programs (all found in the dynvar.spp file), along with an explanation. A few comments for clarification: in each of these I make use of two variables, cur and fdbk. These are defined at the package level. In addition, to improve performance, I open the cursor when the package is initialized and therefore do not need to open and close it in each program.
PROCEDURE assign (expr_in IN VARCHAR2, var_inout IN OUT VARCHAR2) IS BEGIN DBMS_SQL.PARSE (cur, 'BEGIN :var := ''' || expr_in || '''; END;', DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE (cur, 'var', 'a', 2000); fdbk := DBMS_SQL.EXECUTE (cur); DBMS_SQL.VARIABLE_VALUE (cur, 'var', var_inout); END;
Here are the basic steps performed in this procedure:
There are several interesting aspects to dynvar.assign:
FUNCTION val (var_in IN VARCHAR2) RETURN VARCHAR2 IS retval VARCHAR2(2000); BEGIN DBMS_SQL.PARSE (cur, 'BEGIN :val := ' || var_in || '; END;', DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE (cur, 'val', 'a', 2000); fdbk := DBMS_SQL.EXECUTE (cur); DBMS_SQL.VARIABLE_VALUE (cur, 'val', retval); RETURN retval; END;
Here are the steps performed by dynvar.val:
The dynvar. copyto procedure copies the value to the variable named in the second argument. This corresponds to Oracle Forms' COPY and allows you to indirectly reference the variable you want to modify.
PROCEDURE copyto (val_in IN VARCHAR2, nm_in IN VARCHAR2) IS BEGIN DBMS_SQL.PARSE (cur, 'BEGIN ' || nm_in || ' := ''' || val_in || '''; END;', DBMS_SQL.NATIVE); fdbk := DBMS_SQL.EXECUTE (cur); END;
This procedure constructs and parses the string and then executes; there's not much to it. There is no need to call VARIABLE_VALUE because I do not need to extract any values. I am modifying the named variable right in the execution of the PL/SQL block.
I also do not call BIND_VARIABLE, because I have not used any placeholders. Instead, I concatentate directly (into the PL/SQL block) the value I want to assign to the named variable. Why do I do this? Let's examine the consequences of using a placeholder.
Suppose my dynamic PL/SQL block were constructed as follows:
'BEGIN ' || nm_in || ' := :val; END;',
When I bind in my value with a call to DBMS_SQL.BIND_VARIABLE, it is not treated as a literal. For example, if I call dynvar.copyto with the following arguments,
dynvar.copyto ('steven''s hairline', 'rapid.retreat');
then the PL/SQL block I would execute is,
BEGIN rapid.retreat := steven's hairline; END;
which would definitely run into compile problems. When I first encountered this problem, I figured that I would embed the :val placeholder inside single quotes to make sure that the value I pass in was treated as literal. My parse statement then looked like this:
DBMS_SQL.PARSE (cur, 'BEGIN ' || nm_in || ' := '':val''; END;', DBMS_SQL.NATIVE);
But this would not work; DBMS_SQL did not recognize :val as a placeholder, any longer since it was inside single quotes. Sigh. The only solution was to remove :val, and, instead of using placeholders, simply concatenate the value directly into the string inside single quotes.
The dynvar package should get you comfortable with executing dynamic PL/SQL code and also with the concept of indirect referencing. I suggest that you try extending dynvar to support other datatypes (numbers and dates, in particular). See what challenges you encounter. You will definitely need to make some changes to incorporate these different datatypes into your dynamically constructed blocks.
One of the most significant advances for DBMS_SQL in Oracle8 is the support for "array processing" through use of the BIND_ARRAY and DEFINE_ARRAY procedures. Using these procedures, you can greatly speed up dynamic SQL processing that involves multiple rows of data. This section offers some general suggestions and then provides detailed examples of using array processing in DBMS_SQL to perform fetches, queries, and dynamic PL /SQL.
In order to take advantage of array processing, you will need to be comfortable with the use of index-by tables (called PL /SQL tables in Oracle7). These structures are like single-dimension arrays and are described in detail in Chapter 10 of Oracle PL /SQL Programming . With this feature under your belt, you are ready to implement array processing with DBMS_SQL. As you do so, keep in mind the following tips:
The following section shows in more detail how to use array processing in DBMS_SQL to fetch multiple rows with a single call to DBMS_SQL.FETCH_ROWS, update multiple rows of data with multiple bind variables, and manipulate index-by table contents with dynamic PL/SQL.
There are three different ways to change the contents of a table: INSERT, DELETE, and UPDATE. This section shows examples of the first of these DML statements, INSERT, utilizing the array features of DBMS_SQL. The next two sections show examples of DELETE and UPDATE.
The following procedure inserts the contents of a index-by table into a database table using single-row INSERTs (the Version 7 behavior). Notice that I parse once, but bind and execute for each individual row in the index-by table. I do not need to reparse, since I am not changing the SQL statement, only the value I am binding into that SQL statement.
Notice also that I first create a package to define two index-by table types. Why do I do this? I am passing in index-by tables as arguments to my insert procedure. The parameter list of the procedure must therefore reference existing index-by table types in order to compile. I could also have put the table type declarations and the procedure into a single package.
/* Filename on companion disk: dynins.sp */* CREATE OR REPLACE PACKAGE mytables IS TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; END; / CREATE OR REPLACE PROCEDURE instab7 (empnotab IN mytables.number_table, enametab IN mytables.varchar2_table) IS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk PLS_INTEGER; totfdbk PLS_INTEGER := 0; BEGIN DBMS_SQL.PARSE (cur, 'INSERT INTO emp2 (empno, ename) VALUES (:empno, :ename)', DBMS_SQL.NATIVE); FOR emprow IN empnotab.FIRST .. empnotab.LAST LOOP DBMS_SQL.BIND_VARIABLE (cur, 'empno', empnotab(emprow)); DBMS_SQL.BIND_VARIABLE (cur, 'ename', enametab(emprow)); fdbk := DBMS_SQL.EXECUTE (cur); totfdbk := totfdbk + fdbk; END LOOP; DBMS_OUTPUT.PUT_LINE ('Rows inserted: ' || TO_CHAR (totfdbk)); DBMS_SQL.CLOSE_CURSOR (cur); END; /
How would this implementation change with Oracle8? You no longer have to perform separate INSERTs for each of the rows in the index-by table. Instead, you can bind an index-by table directly into the SQL statement. The following version of the instab procedure relies on this new feature. Notice that the FOR LOOP is gone. Instead, I call BIND_ARRAY once for each column in the INSERT statement, passing in the appropriate index-by table. Notice that I must now use the DBMS_SQL table types, whereas in the previous example I created my own index-by table types and used those in the parameter list.
/* Filename on companion disk: dynins.sp */* CREATE OR REPLACE PROCEDURE instab8 (empnotab IN DBMS_SQL.NUMBER_TABLE, enametab IN DBMS_SQL.VARCHAR2_TABLE) IS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk PLS_INTEGER; BEGIN DBMS_SQL.PARSE (cur, 'INSERT INTO emp2 (empno, ename) VALUES (:empno, :ename)', DBMS_SQL.NATIVE); DBMS_SQL.BIND_ARRAY (cur, 'empno', empnotab); DBMS_SQL.BIND_ARRAY (cur, 'ename', enametab); fdbk := DBMS_SQL.EXECUTE (cur); DBMS_OUTPUT.PUT_LINE ('Rows inserted: ' || TO_CHAR (fdbk)); DBMS_SQL.CLOSE_CURSOR (cur); END;
So in this case I end up with less code to write. In other situations, you may have to copy your data from your own index-by tables, perhaps a table or record that is not supported by DBMS_SQL, into the appropriate DBMS_SQL-declared index-by tables before you can call BIND_ARRAY. But putting aside code volume, what about the impact on performance? To compare these two approaches, I wrote the following SQL*Plus script:
/* Filename on companion disk: dynins.tst */* DECLARE timing PLS_INTEGER; empnos7 mytables.number_table; enames7 mytables.varchar2_table; empnos8 DBMS_SQL.NUMBER_TABLE; enames8 DBMS_SQL.VARCHAR2_TABLE; BEGIN /* Load up the index-by tables. */ FOR i IN 1 .. &1 LOOP empnos&2(i) := 10000 + i; enames&2(i) := 'Eli ' || TO_CHAR (i); END LOOP; timing := DBMS_UTILITY.GET_TIME; instab&2 (empnos&2, enames&2); DBMS_OUTPUT.PUT_LINE ('V&2 style = ' || TO_CHAR (DBMS_UTILITY.GET_TIME - timing)); END; /
Though SQL*Plus is a fairly crude tool, those substitution parameters let you write some clever, concise utilities. In this case, I populate my index-by tables with &1 number of rows -- but the index-by table I fill up depends on the &2 or second argument: the Oracle7 or Oracle8 versions. Once the tables have their data, I pass them to the appropriate version of "instab," and, using DBMS_UTILITY.GET_TIME, display the number of hundredths of seconds that elapsed.
I ran this script a number of times to even out the bumps of initial load and parse and so on. The following numbers are representative:
SQL> @dynins.tst 1000 7 Rows inserted: 1000 V7 style = 90 SQL> @dynins.tst 1000 8 Rows inserted: 1000 V8 style = 2
As you can see, dynamic SQL using arrays or index-by tables will perform significantly better than the single-row processing available in Oracle7.
So that's how you do INSERTs: for each column for which you are inserting a value, you must bind in an array or index-by table. You can also mix together scalars and arrays. If, for example, I wanted to insert a set of new employees and make the hiredate the value returned by SYSDATE, the bind steps in my instab8 procedure would be modified as follows:
DBMS_SQL.BIND_ARRAY (cur, 'empno', empnotab); DBMS_SQL.BIND_ARRAY (cur, 'ename', enametab); DBMS_SQL.BIND_VARIABLE (cur, 'hiredate', SYSDATE);
The process for deleting multiple rows with a single call to the EXECUTE function is similar to that for INSERTSs: one call to BIND_ARRAY for each placeholder in the SQL statement. With DELETEs, however, the placeholders are in the WHERE clause, and each row in the index-by table is used to identify one or more rows in the database table.
The following procedure removes all rows as specified in the array of names. Notice the use of LIKE and UPPER operators to increase the flexibility of the table entries (if that is what you want!).
/* Filename on companion disk: dyndel.sp */* CREATE OR REPLACE PROCEDURE delemps (enametab IN DBMS_SQL.VARCHAR2_TABLE) IS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk PLS_INTEGER; BEGIN DBMS_SQL.PARSE (cur, 'DELETE FROM emp WHERE ename LIKE UPPER (:ename)', DBMS_SQL.NATIVE); DBMS_SQL.BIND_ARRAY (cur, 'ename', enametab); fdbk := DBMS_SQL.EXECUTE (cur); DBMS_OUTPUT.PUT_LINE ('Rows deleted: ' || TO_CHAR (fdbk)); DBMS_SQL.CLOSE_CURSOR (cur); END; /
The standard emp table contains the following 14 names:
ADAMS JAMES SCOTT ALLEN JONES SMITH BLAKE KING TURNER CLARK MARTIN WARD FORD MILLER
Now let's run the following script:
And we are then left with the following employees:
ALLEN FORD BLAKE TURNER CLARK WARD
In many situations, you will have the primary key sitting in the row of the index-by table, and the array-based DELETE will simply delete one row of the database table for each row in the array. As you can see from the previous example, however, that is not the only way to use arrays to delete multiple rows.
Finally, we have UPDATE statements, where you can have placeholders both in the SET clause and in the WHERE clause. Be careful about how you utilize arrays in updates; the behavior will not always be as you might expect. Here are some different scenarios or combinations and the behaviors I have encountered:
Generally, you should think of the arrays in the SET clause as being "correlated" to the arrays in the WHERE clause. This correlation is demonstrated in the following procedure. I use dynamic SQL to update the salaries of employees whose names match the strings provided in the enames table.
/* Filename on companion disk: dynupd.sp */* CREATE OR REPLACE PROCEDURE updemps (enametab IN DBMS_SQL.VARCHAR2_TABLE, saltab IN DBMS_SQL.NUMBER_TABLE) IS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk PLS_INTEGER; BEGIN DBMS_SQL.PARSE (cur, 'UPDATE emp SET sal = :sal WHERE ename LIKE UPPER (:ename)', DBMS_SQL.NATIVE); DBMS_SQL.BIND_ARRAY (cur, 'sal', saltab); DBMS_SQL.BIND_ARRAY (cur, 'ename', enametab); fdbk := DBMS_SQL.EXECUTE (cur); DBMS_OUTPUT.PUT_LINE ('Rows updated: ' || TO_CHAR (fdbk)); DBMS_SQL.CLOSE_CURSOR (cur); END; /
I then use the following script to test the procedure. Notice that there are four salaries and only two employee names, each of which is actually a wildcarded pattern.
/* Filename on companion disk: dynupd.tst */* DECLARE timing PLS_INTEGER; sals DBMS_SQL.NUMBER_TABLE; enames DBMS_SQL.VARCHAR2_TABLE; BEGIN /* Load up the index-by tables. */ sals(1) := 1111; sals(2) := 2222; sals(3) := 3333; sals(4) := 4444; enames(1) := '%I%'; /* any name containing an I */ enames(2) := '%S'; /* any name containing an S */ updemps (enames, sals); END; /
When I run this script, I update nine rows as shown in the following output:
SQL> @dynupd.tst Rows updated: 9 ENAME SAL ---------- ---------- SMITH2222 ALLEN1600 WARD1250 JONES2222 MARTIN1111 BLAKE2850 CLARK2450 SCOTT2222 KING1111 TURNER1500 ADAMS2222 JAMES2222 FORD3000 MILLER1111
What has happened here? All the employees with an I in their name have a salary of 1111, and all the employees with S in their name have a salary of 2222. If you have both an I and S, as with SMITH, notice that you get the S salary of 2222 and not the I salary of 1111. The salaries of 3333 and 4444 are completely ignored by the procedure, since there are only two rows in the enames table.
For a final example of array processing for updates, the following program reads data from a file and performs a batch update of employee salaries, correlating the key value (first column in the file's line) with the new column value (second column in the file's line).
/* Filename on companion disk: fileupd.sp */* CREATE OR REPLACE PROCEDURE upd_from_file (loc IN VARCHAR2, file IN VARCHAR2) IS /* DBMS_SQL related elements */ cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk PLS_INTEGER; empnos DBMS_SQL.NUMBER_TABLE; sals DBMS_SQL.NUMBER_TABLE; /UTL_FILE related elements */ fid UTL_FILE.FILE_TYPE; v_line VARCHAR2(2000); v_space PLS_INTEGER; BEGIN /* Load the index-by tables from the file. */ fid := UTL_FILE.FOPEN (loc, file, 'R'); BEGIN LOOP UTL_FILE.GET_LINE (fid, v_line); v_space := INSTR (v_line, ' ', 1, 1); empnos (NVL (empnos.LAST, 0) + 1) := TO_NUMBER (SUBSTR (v_line, 1, v_space-1)); sals (NVL (empnos.LAST, 0) + 1) := TO_NUMBER (SUBSTR (v_line, v_space+1)); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN UTL_FILE.FCLOSE (fid); END; /* Perform the multiple row updates. */ DBMS_SQL.PARSE (cur, 'UPDATE emp SET sal = :sal WHERE empno = :empno', DBMS_SQL.NATIVE); DBMS_SQL.BIND_ARRAY (cur, 'empno', empnos); DBMS_SQL.BIND_ARRAY (cur, 'sal', sals); fdbk := DBMS_SQL.EXECUTE (cur); DBMS_SQL.CLOSE_CURSOR (cur); END; /
You can run this procedure against the fileupd.dat data file by executing the fileupd.tst script to confirm the results. In this case, each row in the empnos index-by table identifies a specific record in the database; the corresponding row in the sals index-by table is then used in the update of the sal column value. Notice that I need to put the loop that reads the file inside its own block, because the only way to know that I have read the whole file is to call UTL_FILE.GET_LINE until it raises a NO_DATA_FOUND exception (which means "EOF" or end of file).
As a final treat, check out the package in the arrayupd.spp file (and the corresponding arrayupd.tst test script). It offers a completely dynamic interface allowing array-based updates of any number, date, or string column in any table based on a single integer primary key. For example, using the arrayupd.col procedure, all of the dynamic SQL code in the upd_from_file procedure could be replaced with the following:
BEGIN ... Load arrays from file as before ... /* Then call arrayupd to perform the array-based update. */ arrayupd.cols ('emp', 'empno', 'sal', empnos, sals); END;
And the really wonderful thing about this overloaded procedure is that while it certainly is not as fast as static UPDATE statements, it competes very nicely and is efficient enough for many applications.
For all the value of array processing to update a database table, you are most likely to use BIND_ARRAY and DEFINE_ARRAY to fetch rows from the database and then process them in a front-end application. If the claims of Oracle Corporation about improved performance of dynamic SQL are true (see Chapter 10 of Oracle PL/SQL Programming ), then this array-processing feature of DBMS_SQL could offer a crucial solution to the problem of passing multiple rows of data between the server and the client application through a PL/SQL application.
Let's examine how to perform fetches with arrays in DBMS_SQL -- and analyze the performance impact -- by building a package called empfetch to retrieve employee numbers and names. In the process, I will construct a "wrapper" around this fantastic new technology so that it can be made available in client environments where index-by tables and other Oracle8 features are not available directly.
First, the basics of querying into arrays: you must use the DEFINE_ARRAY procedure to define a specific column in the cursor as an array column. When you do this, you also specify the number of rows which are to be fetched in a single call to FETCH_ROWS (or EXECUTE_AND_FETCH). Here, for example, is the code required to set up a cursor to retrieve 100 rows from the orders table:
DECLARE ordernos DBMS_SQL.NUMBER_TABLE; orderdates DBMS_SQL.DATE_TABLE; cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR BEGIN DBMS_SQL.PARSE (cur, 'SELECT orderno, order_date FROM orders', DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_ARRAY (cur, 1, ordernos, 100, 1); DBMS_SQL.DEFINE_ARRAY (cur, 1, orderdates, 100, 1);
You then use COLUMN_VALUE to retrieve column values, just as you would with scalar, non-array processing. You simply provide an array to accept the multiple values, and in they come.
Moving on to our example, here is the specification for the package to query employee data using dynamic SQL and array processing:
/* Filename on companion disk: empfetch.spp */* CREATE OR REPLACE PACKAGE empfetch IS PROCEDURE rows (numrows_in IN INTEGER, where_clause_in IN VARCHAR2 := NULL, append_rows_in IN BOOLEAN := FALSE); FUNCTION ename_val (row_in IN INTEGER) RETURN emp.ename%TYPE; FUNCTION empno_val (row_in IN INTEGER) RETURN emp.empno%TYPE; FUNCTION numfetched RETURN INTEGER; END empfetch; /
The empfetch. rows procedure fetches the specified maximum number of rows from the database, using an optional WHERE clause. You can also request "append rows," which means that the newly fetched rows are appended to employee numbers and names already in the index-by tables. The default behavior is to delete all existing rows in the arrays.
Once the rows are loaded with a call to empfetch.rows, you can retrieve the Nth employee name and employee number, as well as find out how many rows were fetched. This is a nice enhancement over normal cursor-based processing: this way, I have random, bidirectional access to my data.
Finally, notice that there is no indication in this package specification that I am using dynamic SQL or array processing or index-by tables. These programs are callable from any environment supporting the most basic versions of PL/SQL, from 1.1 in Oracle Developer/2000 Release 1 to any variant of PL/SQL Release 2.X. This is especially important for third-party tools like PowerBuilder, which do not always keep up with the latest enhancements of PL/SQL.
Before exploring the implementation of the empfetch package, let's see an example of its use, combined with an analysis of its performance. Once the package compiled, I built a script to compare using static and dynamic SQL to fetch rows from the database table. The empfetch.tst script first uses an explicit cursor to fetch all the rows from the emp table and copy the ename and empno column values to local variables. Can't get much leaner than that. I then use the empfetch package to perform the same steps.
/* Filename on companion disk: empfetch.tst */ DECLARE timing PLS_INTEGER; v_ename emp.ename%TYPE; v_empno emp.empno%TYPE; BEGIN /* The static approach */ timing := DBMS_UTILITY.GET_TIME; FOR i IN 1 .. &1 LOOP DECLARE CURSOR cur IS SELECT empno, ename FROM emp; BEGIN FOR rec IN cur LOOP v_ename := rec.ename; v_empno := rec.empno; END LOOP; END; END LOOP; DBMS_OUTPUT.PUT_LINE ('static = ' || TO_CHAR (DBMS_UTILITY.GET_TIME - timing)); timing := DBMS_UTILITY.GET_TIME; FOR i IN 1 .. &1 LOOP /* Fetch all the rows from the table, putting them in arrays maintained inside the package body. */ empfetch.rows (20); /* For each row fetched, copy the values from the arrays to the local variables by calling the appropriate functions. Notice that there is no exposure here of the fact that arrays are being used. */ FOR i IN 1 .. empfetch.numfetched LOOP v_ename := empfetch.ename_val (i); v_empno := empfetch.empno_val (i); END LOOP; END LOOP; DBMS_OUTPUT.PUT_LINE ('dynamic = ' || TO_CHAR (DBMS_UTILITY.GET_TIME - timing)); END; /
SQL> @empfetch.tst 10 static = 114 dynamic = 119 SQL> @empfetch.tst 100 static = 1141 dynamic = 1167
In other words, there was virtually no difference in performance between these two approaches. This closeness exceeded my expectations, and is more than one might expect simply from the array processing. My hat is off to the PL/SQL development team! They really have reduced the overhead of executing dynamic SQL and anonymous blocks.
You will find the full implementation of the empfetch body in the empfetch.spp file; later I will show the different elements of the package and offer some observations. First, the following data structures are declared at the package level:
Now we will look at the programs that make use of these data structures. First, the rows procedure, which populates the arrays:
PROCEDURE rows (numrows_in IN INTEGER, where_clause_in IN VARCHAR2 := NULL, append_rows_in IN BOOLEAN := FALSE) IS v_start PLS_INTEGER := 1; BEGIN IF append_rows_in THEN v_start := NVL (GREATEST (empno_array.LAST, ename_array.LAST), 0) + 1; ELSE /* Clean out the tables from the last usage. */ empno_array.DELETE; ename_array.DELETE; END IF; /* Parse the query with a dynamic WHERE clause */ DBMS_SQL.PARSE (c, 'SELECT empno, ename FROM emp WHERE ' || NVL (where_clause_in, '1=1'), DBMS_SQL.NATIVE); /* Define the columns in the cursor for this query */ DBMS_SQL.DEFINE_ARRAY (c, 1, empno_array, numrows_in, v_start); DBMS_SQL.DEFINE_ARRAY (c, 2, ename_array, numrows_in, v_start); /* Execute the query and fetch the rows. */ g_num_fetched:= DBMS_SQL.EXECUTE_AND_FETCH (c); /* Move the column values into the arrays */ DBMS_SQL.COLUMN_VALUE (c, 1, empno_array); DBMS_SQL.COLUMN_VALUE (c, 2, ename_array); END;
Areas of interest in this program include:
That takes care of most of the work and complexity of the empfetch package. Let's finish up by looking at the functions that retrieve individual values from the arrays. These are very simple pieces of code; I will show ename_val, but empno_val is almost exactly the same:
Notice that I avoid raising the NO_DATA_FOUND exception by checking whether the row exists before I try to return it.
As you can see from empfetch, it doesn't necessarily take lots of code to build a solid encapsulation around internal data structures. From the performance of this package, you can also see that array processing with dynamic SQL offers some new opportunities for building an efficient programmatic interface to your data.
The first release of Oracle8 (8.0.3) contained a number of bugs relating to the use of array processing in dynamic PL/SQL. The 8.0.4 and 8.0.5 releases, however, fix many (if not all) of these problems. I offer in this section one example to demonstrate the techniques involved in order to copy the contents of one index table to another index table. You can find another example of array processing with index tables in the next section.
The following testarray procedure moves the rows of one index table to another.
on companion disk: plsqlarray.sp */* CREATE OR REPLACE PROCEDURE testarray IS cur INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk INTEGER; mytab1 DBMS_SQL.NUMBER_TABLE; mytab2 DBMS_SQL.NUMBER_TABLE; BEGIN mytab1 (25) := 100; mytab1 (100) := 1000; mytab2 (25) := -100; mytab2 (100) := -1000; DBMS_SQL.PARSE (cur, 'BEGIN :newtab := :oldtab; END;', DBMS_SQL.NATIVE); DBMS_SQL.BIND_ARRAY (cur, 'newtab', mytab2, 25, 100); DBMS_SQL.BIND_ARRAY (cur, 'oldtab', mytab1, 25, 100); fdbk := DBMS_SQL.EXECUTE (cur); DBMS_SQL.VARIABLE_VALUE (cur, 'newtab', mytab2); DBMS_OUTPUT.PUT_LINE('mytab2(1) := ' || mytab2(1)); DBMS_OUTPUT.PUT_LINE('mytab2(2) := ' || mytab2(2)); DBMS_OUTPUT.PUT_LINE('mytab2(25) := ' || mytab2(25)); DBMS_OUTPUT.PUT_LINE('mytab2(100) := ' || mytab2(100)); DBMS_SQL.CLOSE_CURSOR (cur); END; /
Notice that I must call DBMS_SQL.BIND_VARIABLE twice, once for the "IN" index table (the "old table," mytab1) and once for the "OUT" index table (the "new table," mytab2). Finally, I call DBMS_SQL.VARIABLE_VALUE to transfer the result of the assignment into my local index table.
Here are the results from execution of this procedure:
SQL> exec testarray mytab2(1) := -1000 mytab2(2) := 100 mytab2(25) := -100 mytab2(100) := -1000
Ah! Not quite what we might have expected. This procedure did transfer the contents of mytab1 to mytab2, but it filled rows sequentially in mytab2 starting from row 1 -- definitely something to keep in mind when you take advantage of this technology. You might want to DELETE from the index table before the copy.
One of the many enhancements in Oracle8 is the addition of the RETURNING clause to INSERTs, UPDATEs, and DELETEs. You can use the RETURNING clause to retrieve data (or expressions derived from the data) from the rows affected by the DML statement.
Here is an example of an INSERT statement that returns the just-generated primary key:
DECLARE mykey emp.empno%TYPE; total_comp NUMBER; BEGIN INSERT INTO emp (empno, ename, deptno) VALUES (emp_seq.NEXTVAL, 'Feuerstein', 10) RETURNING empno, sal + NVL (comm, 0) INTO mykey, total_comp; END; /
You return data into scalar values when the DML has modified only one row. If the DML modifies more than one row, you can return data into index tables using DBMS_SQL. If you modify more than one row and try to return values into a scalar variable, then you will raise an exception, as follows:
SQL> DECLARE mykey emp.empno%TYPE; BEGIN /* Updating all rows in the table... */ UPDATE emp SET sal = 1000 RETURNING empno INTO mykey; END; / ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows
If you are changing more than one row of data and employing the RETURNING clause, you must use DBMS_SQL to pass back those values into an index table. You cannot simply specify an index table in the RETURNING clause with static SQL. If you try this, you will receive the following error:
PLS-00385: type mismatch found at '<index table>' in SELECT...INTO statement
The rest of this section demonstrates the use of the RETURNING clause for both single- and multiple-row operations from within DBMS_SQL.
Suppose that you want to insert a row into a table with the primary key generated from a sequence, and then return that sequence value to the calling program. In this example, I will create a table to hold notes (along with a sequence and an index, the latter to show the behavior when an INSERT fails):
I then build a procedure around the INSERT statement for this table:
/* Filename on companion disk: insret.sp */* CREATE OR REPLACE PROCEDURE ins_note (text_in IN note.text%TYPE, note_id_out OUT note.note_id%TYPE) IS cur INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk INTEGER; v_note_id note.note_id%TYPE; BEGIN DBMS_SQL.PARSE (cur, 'INSERT INTO note (note_id, text) ' || ' VALUES (note_seq.NEXTVAL, :newtext) ' || ' RETURNING note_id INTO :newid', DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE (cur, 'newtext', text_in); DBMS_SQL.BIND_VARIABLE (cur, 'newid', v_note_id); fdbk := DBMS_SQL.EXECUTE (cur); DBMS_SQL.VARIABLE_VALUE (cur, 'newid', v_note_id); note_id_out := v_note_id; DBMS_SQL.CLOSE_CURSOR (cur); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Note insert failure:'); DBMS_OUTPUT.PUT_LINE (SQLERRM); END; /
These operations should be familiar to you by this time. Here are the steps to perform:
The following anonymous block tests this procedure by inserting a row and then trying to insert it again:
Here is the result:
SQL> @insret.sp New primary key = 41 Note insert failure: ORA-00001: unique constraint (SCOTT.NOTE_TEXT_IND) violated
The same technique and steps can be used for using the RETURNING clause in UPDATEs and DELETEs when affecting single rows. Let's now take a look at the slightly more complex scenario: changing multiple rows of data and returning values from those rows.
I want to use dynamic SQL to provide a generic delete mechanism for tables with single integer primary keys. You provide the name of the table, the name of the primary key, and the WHERE clause. I do the delete. But such a general utility will never be used unless it can be trusted, unless its actions can be confirmed. That is where the RETURNING clause comes into play.
/* Filename on companion disk: delret.sp */* CREATE OR REPLACE PROCEDURE delret (tab_in IN VARCHAR2, pkey_in IN VARCHAR2, where_in IN VARCHAR2, pkeys_out OUT DBMS_SQL.NUMBER_TABLE) IS cur INTEGER := DBMS_SQL.OPEN_CURSOR; delstr VARCHAR2(2000) := 'DELETE FROM ' || tab_in || ' WHERE ' || where_in || ' RETURNING ' || pkey_in || ' INTO :idarray'; fdbk INTEGER; v_pkeys DBMS_SQL.NUMBER_TABLE; BEGIN DBMS_OUTPUT.PUT_LINE ('Dynamic Delete Processing: '); DBMS_OUTPUT.PUT_LINE (delstr); DBMS_SQL.PARSE (cur, delstr, DBMS_SQL.NATIVE); DBMS_SQL.BIND_ARRAY (cur, 'idarray', v_pkeys); fdbk := DBMS_SQL.EXECUTE (cur); DBMS_SQL.VARIABLE_VALUE (cur, 'idarray', v_pkeys); pkeys_out := v_pkeys; DBMS_SQL.CLOSE_CURSOR (cur); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Dynamic Delete Failure:'); DBMS_OUTPUT.PUT_LINE (SQLERRM); END; /
In this program, I construct the DELETE statement and save it to a local variable. I do this so that I can display the string for confirmation purposes and then parse it without duplicating the code. I will go to great lengths (and do so on a regular basis) to avoid even the slightest repetition of code.
When I bind, I call DBMS_SQL.BIND_ARRAY instead of DBMS_SQL.BIND_VARIABLE so that I can retrieve an entire list of values. My call to DBMS_SQL.VARIABLE_VARIABLE performs the retrieval, and I close the cursor. The following anonymous block exercises my generic "delete and return" procedure. Notice that since I am returning an index table, I must always declare such a table to hold the values.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.