Section 16.6.  When to Use DBMS_SQL

16.6. When to Use DBMS_SQL Native dynamic SQL should be your first choice (over DBMS_SQL) to satisfy dynamic SQL requirements in your PL/SQL programs for the following reasons: NDS is much easier to write; you need less code, and the code you write is more intuitive, leading to many fewer bugs. The code is also much easier to maintain. NDS works with all SQL datatypes, including user-defined objects and collection types (associative arrays, nested tables, and VARRAYs). DBMS_SQL works only with Oracle7 Database-compatible datatypes. There are, however, situations when you will want or need to use DBMS_SQL. The following sections describe these situations. 16.6.1. Parse Very Long Strings EXECUTE IMMEDIATE executes a single string, with a maximum length of 32K. What if your SQL statement exceeds that length? While that scenario is unlikely for any SQL statements you construct yourself, generated dynamic SQL statements based on tables with many columns (you can now have up to 1,000 columns in a table) could easily exceed that limit. DBMS_SQL to the rescue! Use a special overloading of DBMS_SQL.PARSE to parse arbitrarily long SQL and PL/SQL statements by passing a collection to the built-in that contains the full text of the dynamic statement. The collection must be of type DBMS_SQL.VARCHAR2S (maximum bytes per line is 256) or DBMS_SQL.VARCHAR2A (maximum bytes per line is 32,676). The following example of compiling a program defined in a file will demonstrate the necessary steps. Here I focus on the DBMS_SQL-specific steps; please check the file for the full implementation. /* File on web: compile_from_file.sql */ 1 CREATE OR REPLACE PROCEDURE compile_from_file ( 2 dir_in IN VARCHAR2, file_in IN VARCHAR2) 3 IS 4 l_file UTL_FILE.file_type; 5 l_lines DBMS_SQL.varchar2s; 6 l_cur PLS_INTEGER := DBMS_SQL.open_cursor; 7 8 PROCEDURE read_file (lines_out IN OUT DBMS_SQL.varchar2s) IS 9 BEGIN 10 l_file := UTL_FILE.fopen (dir_in, file_in, 'R'); 11 LOOP 12 UTL_FILE.get_line (l_file, l_lines (lines_out.COUNT + 1)); 13 END LOOP; 14 EXCEPTION 15 WHEN NO_DATA_FOUND THEN UTL_FILE.fclose (l_file); 16 END read_file; 17 BEGIN 18 read_file (l_lines); 19 DBMS_SQL.parse (l_cur, l_lines 20 , l_lines.FIRST, l_lines.LAST 21 , TRUE, DBMS_SQL.native); 22 DBMS_SQL.close_cursor (l_cur); 23 * END compile_from_file; Here is an explanation of the key sections of this program: Line(s) Significance 5-6 Declare the l_lines local collection based on the DBMS_SQL type; then declare and open a cursor for use by DBMS_SQL.PARSE 11-13 Transfer the contents of the file to the l_lines collection 19-21 Call the collection-based overloading of DBMS_SQL.PARSE, passing it the collection, and specifying that the entire contents of the collection be used (from l_lines.FIRST to l_lines.LAST) 16.6.2. Obtain Information About Query Columns DBMS_SQL allows you to describe the columns of your dynamic cursor, returning information about each column in an associative array of records. This capability offers the possibility of writing very generic cursor-processing code; this program may come in particularly handy when you are writing Method 4 dynamic SQL, and you are not certain how many columns are being selected. When you call this program, you need to have declared a PL/SQL collection based on the DBMS_SQL.DESC_TAB collection type (or DESC_TAB2, if your query might return column names that are greater than 30 characters in length). You can then use collection methods to traverse the table and extract the needed information about the cursor. The following anonymous block shows the basic steps you will perform when working with this built-in: DECLARE cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; cols DBMS_SQL.DESC_TAB; ncols PLS_INTEGER; BEGIN -- Parse the query. DBMS_SQL.PARSE (cur, 'SELECT hiredate, sal FROM emp', DBMS_SQL.NATIVE); -- Retrieve column information DBMS_SQL.DESCRIBE_COLUMNS (cur, ncols, cols); -- Display each of the column names FOR colind IN 1 .. ncols LOOP DBMS_OUTPUT.PUT_LINE (cols.col_name); END LOOP; DBMS_SQL.CLOSE_CURSOR (cur); END; / To make it easier for you to use DESCRIBE_COLUMNS, I have created a package that hides much of the underlying detail. Here is the package specification: /* File on web: desccols.pkg */ 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;   FUNCTION for_query (sql_in IN VARCHAR2) RETURN DBMS_SQL.desc_tab;   FUNCTION for_cursor (cur IN PLS_INTEGER) RETURN DBMS_SQL.desc_tab;   PROCEDURE show ( col_list_in IN DBMS_SQL.desc_tab , fst IN PLS_INTEGER := 1 , lst IN PLS_INTEGER := NULL ); END desccols; Notice that I provide named constants for each of the column datatypes returned in the DBMS_SQL collection. Use these constants to avoid hardcoding values when you need to check on the type of data in a column. You can also use the for_query function when you want to get information about the columns of a dynamic query, but might not otherwise be using DBMS_SQL. Here is a script demonstrating the usage of this package: /* File on web: desccols.tst */ DECLARE cur INTEGER := DBMS_SQL.open_cursor; tab DBMS_SQL.desc_tab; BEGIN DBMS_SQL.parse (cur , 'SELECT ename, sal, hiredate FROM emp' , DBMS_SQL.native ); tab := desccols.for_cursor (cur); desccols.show (tab); DBMS_SQL.close_cursor (cur); -- tab := desccols.for_query ('SELECT * FROM emp'); desccols.show (tab); END; / 16.6.3. Meet Method 4 Dynamic SQL Requirements DBMS_SQL supports Method 4 dynamic SQL (variable number of columns selected of variables bound) more naturally than NDS. We have already seen that in order to implement Method 4 with NDS, you must switch to dynamic PL/SQL, which is generally a higher level of abstraction than many developers want to deal with. When would you run into Method 4? It certainly arises when you build a front end 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. Let's step through the implementation of a variation on this theme: the construction of a PL/SQL procedure to display the contents of a tableany table, as specified by the user at runtime. Here I cover only those aspects pertaining to the dynamic SQL itself; check out the intab.sp file on the book's web site for the full implementation. 16.6.3.1 The "in table" procedural interface 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. Parameter Description Name of the table Required. Obviously, a key input to this program. Maximum length of string displayed Optional. Sets an upper limit on the size of string columns. I do not even attempt to do the kind of string wrapping performed in SQL*Plus. Instead, SUBSTR simply truncates the values. WHERE clause Optional. Allows you to restrict the rows retrieved by the query. If not specified, all rows are retrieved. You can also use this parameter to pass in ORDER BY and HAVING clauses, because they follow immediately after the WHERE clause. Format for date columns Optional. Allows you to set the standard format for date displays. The default includes date and time information. When using SQL*Plus, I find it very irritating to constantly have to use TO_CHAR to see the time portion of my date fields. Given these inputs, the specification for my procedure becomes the following: PROCEDURE intab (table_in IN VARCHAR2, string_length_in IN INTEGER DEFAULT 20, where_in IN VARCHAR2 DEFAULT NULL, date_format_in IN VARCHAR2 DEFAULT 'MM/DD/YY HHMISS') Here are some examples of calls to intab, along with their output. First, the entire contents of the emp table: SQL> EXEC intab ('emp'); ----------------------------------------------------------------------- _ Contents of emp ----------------------------------------------------------------------- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----------------------------------------------------------------------- 7369 SMITH CLERK 7902 12/17/80 120000 800 20 7499 ALLEN SALESMAN 7698 02/20/81 120000 1600 300 30 7521 WARD SALESMAN 7698 02/22/81 120000 1250 500 30 7566 JONES MANAGER 7839 04/02/81 120000 2975 20 7654 MARTIN SALESMAN 7698 09/28/81 120000 1250 1400 30 7698 BLAKE MANAGER 7839 05/01/81 120000 2850 30 7782 CLARK MANAGER 7839 06/09/81 120000 2450 10 7788 SCOTT ANALYST 7566 04/19/87 120000 3000 20 7839 KING PRESIDENT 11/17/81 120000 5000 10 7844 TURNER SALESMAN 7698 09/08/81 120000 1500 0 30 7876 ADAMS CLERK 7788 05/23/87 120000 1100 20 7900 JAMES CLERK 7698 12/03/81 120000 950 30 7902 FORD ANALYST 7566 12/03/81 120000 3000 20 And now let's see just those employees in department 10, specifying a maximum length of 20 characters for string columns: SQL> EXEC intab ('emp', 20, 'deptno = 10 ORDER BY sal'); ----------------------------------------------------------------------- _ Contents of emp ----------------------------------------------------------------------- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----------------------------------------------------------------------- 7934 MILLER CLERK 7782 01/23/82 120000 1300 10 7782 CLARK MANAGER 7839 06/09/81 120000 2450 10 7839 KING PRESIDENT 11/17/81 120000 5000 10 And now an entirely different table, with a different number of columns: SQL> EXEC intab ('dept') ------------------------------------ _ Contents of dept ------------------------------------ DEPTNO DNAME LOC ------------------------------------ 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Notice that the user does not have to provide any information about the structure of the table. My program will get that information itselfprecisely the aspect of intab that makes it a Method 4 dynamic SQL example. 16.6.3.2 Steps for intab construction To display the contents of a table, follow these steps: Construct and parse the SELECT statement (using OPEN_CURSOR and PARSE). Bind all local variables with their placeholders in the query (using BIND_ VARIABLE). Define each column in the cursor for this query (using DEFINE_COLUMN). Execute and fetch rows from the database (using EXECUTE and FETCH_ ROWS). Retrieve values from the fetched row, and place them into a string for display purposes (using COLUMN_VALUE). Then display that string with a call to the PUT_LINE procedure of the DBMS_OUTPUT package. My intab implementation does not currently support bind variables. I assume, in other words, that the where_clause_in argument does not contain any bind variables. As a result, I will not be exploring in detail the code required for step 2. 16.6.3.3 Constructing the SELECT To extract the data from the table, I have to construct the SELECT 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 the ALL_TAB_COLUMNS view in the intab procedure so the user can view the contents not only of tables he owns (which are accessible in USER_TAB_COLUMNS), but also any table for which he 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 waysfor example: To build the select list for the query, I will use the column names. To display the output of a table in a readable fashion, I need to provide a column header that shows the names of the columns over their data. These column names must be spaced out across the line of data in, well, columnar format. So I need the column name and the length of the data for that column. To fetch data into a dynamic cursor, I need to establish the columns of the cursor with calls to DEFINE_COLUMN. For this, I need the column datatype and length. To extract the data from the fetched row with COLUMN_VALUE, I need to know the datatypes of each column, as well as the number of columns. To display the data, I must construct a string containing all the data (using TO_CHAR to convert numbers and dates). Again, I must pad out the data to fit under the column names, just as I did with the header line. 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 the column data out of the ALL_TAB_COLUMNS view, I will store that data in three PL/SQL collections: Collection Description colname The names of each column coltype The datatypes of each column, a string describing the datatype collen The number of characters required to display the column data 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. Calculating the column length is a bit trickier, but also not crucial to learning how to write Method 4 dynamic SQL. I will leave it to the reader to study the file. I apply all of my logic inside a cursor FOR loop that sweeps through all the columns for a table (as defined in ALL_COLUMNS). This loop (shown in the following example) fills my PL/SQL collection: 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;   /* 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 collections with the column information I need for calls to DBMS_SQL.DEFINE_COLUMN and DBMS_SQL.COLUMN_VALUE, and also created the column header line. Now that was a busy loop! Now it is time to parse the query, and then construct the various columns in the dynamic cursor object. 16.6.3.4 Defining the cursor structure The parse phase is straightforward enough. I simply cobble together the SQL statement from its processed and refined components, including, most notably, the column list I just constructed (the col_list variable): 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. With DBMS_SQL, 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, I 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 hardcode the number or type of calls to DBMS_SQL.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 DBMS_SQL.DEFINE_COLUMN for each row defined in my PL/SQL table colname. Before we go through the actual code, here are some reminders about DBMS_SQL.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: The second argument is a number; DBMS_SQL.DEFINE_COLUMN does not work with column namesonly with the sequential position of the column in the list. The third argument establishes the datatype of the cursor's column. It does this by accepting an expression of the appropriate type. You do not, in other words, pass a string such as "VARCHAR2" to DBMS_SQL.DEFINE_COLUMN. Instead, you would pass a variable defined as VARCHAR2. When you are defining a character-type column, you must also specify the maximum length of values retrieved into the cursor. In the context of the intab procedure, 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 DBMS_SQL.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. Specify: 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. 16.6.3.5 Retrieving and displaying data 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 that 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 DBMS_SQL.COLUMN_VALUE. I call this built-in for each column in the table (information that is stored inyou guessed itmy 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. Again, check out intab.sp for the full details; the intab_dbms_sql.sp file also contains a version of this procedure that is updated to take advantage of Oracle 9i Database features and is more fully documented. 16.6.4. Minimize Parsing of Dynamic Cursors One of the drawbacks of EXECUTE IMMEDIATE is that each time the dynamic string is executed it will be reprepared, which will usually involve parsing, optimization, and plan generation. For most dynamic SQL requirements, the overhead of these steps will be compensated for by other benefits of NDS (in particular, the avoidance of calls to a PL/SQL API as happens with DBMS_SQL). In some cases, however, the parse phase may be quite expensive. For such scenarios, DBMS_SQL may be a better solution, precisely for the same reason that usually makes this built-in package unattractive: you have control overand have to code foreach explicit step in the process. With DBMS_SQL, you can explicitly avoid the parse phase when you know that the SQL string you are executing dynamically is changing only its bind variables. All you have to do is avoid calling DBMS_SQL.PARSE again, and simply rebind the variable values with calls to DBMS_SQL.BIND_VARIABLE. Let's look at a very simple example, demonstrating the specific calls you make to the DBMS_SQL package. The following anonymous block executes a dynamic query inside a loop: 1 DECLARE 2 l_cursor pls_INTEGER; 3 l_result pls_INTEGER; 4 BEGIN 5 FOR i IN 1 .. counter 6 LOOP 7 l_cursor := DBMS_SQL.open_cursor; 8 DBMS_SQL.parse 9 (l_cursor, 'SELECT ... where col = ' || i , DBMS_SQL.native); 10 l_result := DBMS_SQL.EXECUTE (l_cursor); 11 DBMS_SQL.close_cursor (l_cursor); 12 END LOOP; 13 * END;; Within my loop, I take the following actions: Line(s) Description 7 Obtain a cursor, simply a pointer to memory used by DBMS_SQL 8-9 Parse the dynamic query, after concatenating in the only variable element, the variable i 10 Execute the query 11 Close the cursor This is all valid (and, of course, you would usually follow up the execution of the query with fetch and retrieve steps), yet it also is a misuse of DBMS_SQL. Consider the following rewrite of the same steps: 1 DECLARE 2 l_cursor PLS_INTEGER; 3 l_result PLS_INTEGER; 4 BEGIN 5 l_cursor := DBMS_SQL.open_cursor; 6 DBMS_SQL.parse (l_cursor, 'SELECT ... WHERE col = :value' 7 , DBMS_SQL.native); 8   9 FOR i IN 1 .. counter 10 LOOP 11 DBMS_SQL.bind_variable (l_cursor, 'value', i); 12 l_result := DBMS_SQL.EXECUTE (l_cursor); 13 END LOOP; 14   15 DBMS_SQL.close_cursor (l_cursor); 16 * END; In this usage of DBMS_SQL, I now declare the cursor only once, because I can reuse the same cursor with each call to DBMS_SQL.PARSE. I also move the parse call outside of the cursor. Because the structure of the SQL statement itself doesn't change, I don't need to reparse for each new value of i. So I parse once and then, within the loop, bind a new variable value into the cursor, and execute. When I am all done (after the loop terminates), I close the cursor. The ability to perform each step explicitly and separately gives developers enormous flexibility (and also headaches from all the code and complexity of DBMS_SQL). If that is what you need, DBMS_SQL is hard to beat. If you do use DBMS_SQL in your application, I encourage you to take advantage of the package found in the dynalloc.pkg file. This "dynamic allocation" package helps you to: Minimize cursor allocation through cursor reuse Perform tight and useful error handling for all DBMS_SQL parse operations Avoid errors trying to open or close cursors that are already opened or closed