2.5.4.1 The "in table" procedural interface

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:

• Supplying a list of only those columns you wish to display. This will bypass the full list of columns for the table (which, as you will see, is extracted from the data dictionary).

• Supplying a list of those columns you wish to exclude from display. If your table has 50 columns, and you don't want to display three of those columns, it's a lot easier to list the three you don't want than the 47 you do want.

• Supplying an ORDER BY clause for the output. You could do this through the WHERE clause, but it is certainly more structured to provide a separate input.

• Providing a format for numeric data in addition to the date format.

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.)

2.5.4.2 Steps for intab construction

In order to display the contents of a table, follow these steps:

1. Construct and parse the SELECT statement (using OPEN_CURSOR and PARSE).

2. Bind all local variables with their placeholders in the query (using BIND_VARIABLE).

3. Define each column in the cursor for this query (using DEFINE_COLUMN).

4. Execute and fetch rows from the database (using EXECUTE and FETCH_ROWS).

5. 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.

NOTE: 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.

2.5.4.3 Constructing the SELECT

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:

• I will use the column names to build the select list for the query.

• 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 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.

2.5.4.4 Computing column length

I need to take several different aspects of the column into account:

1. The length of the column name (you don't want the column length to be smaller than the header).

2. The maximum length of the data. If it's a string column, that information is contained in the data_length column of all_tab_columns.

3. If it's a number column, that information is contained in the data_precision column of the view (unless the datatype is unconstrained, in which case that information is found in the data_length column).

4. If it's a date column, the number of characters will be determined by the length of the date format mask.

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.

2.5.4.5 Defining the cursor structure

The parse phase is straightforward enough. I simply cobble together the SQL statement from its processed and refined components.

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:

1. The second argument is a number; DEFINE_COLUMN does not work with column names  -- only with the sequential position of the column in the list.

2. 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 like "VARCHAR2" to DEFINE_COLUMN. Instead, you would pass a variable defined as VARCHAR2.

3. 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 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.

2.5.4.6 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 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.

2.5.4.7 Build those utilities!

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!

2.5.4.8 Full text of intab procedure

Because the full intab procedure is so long, I haven't included it here. For the full text of this procedure, see the intab.sp file on the companion disk.

2.5.5.1 Assigning a value

The dynvar. assign procedure performs a dynamic assignment of a string expression to a string variable. That variable can be either locally or globally (in a package) defined.

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:

• Parse the assignment statement constructed with a bind variable and the expression. (Remember that the cursor was previously opened and remains open for the duration of your session.)

• Bind the variable (even though it is an OUT value) that will receive the value of the expression. The value I bind into it is irrelevant, but dynamic PL/SQL requires that you call the BIND_VARIABLE procedure for each placeholder in your string.

• Execute the PL/SQL block.

• Extract the value assigned to the placeholder variable and pass it to the variable you provided in the call to the procedure.

There are several interesting aspects to dynvar.assign:

• In the parse, I construct my assignment string, making sure to enclose the expression within single quotes. I do this because expr_in will be evaluated to a string when it is passed into the procedure. If I do not enclose it in quotes, the PL/SQL compiler will try to interpret the string as code rather than as a literal.

• The second argument to dynvar.assign must be declared as IN OUT because, when I perform the call to the VARIABLE_VALUE procedure, I deposit a new value directly into the variable provided in the call to the procedure.

• When you call dynvar.assign, you pass the actual variable in the second argument, not a string (whether literal or a variable) containing the name of the variable. If you want to assign a value to a variable, but reference the variable by its name, use the dynvar.copyto procedure.

2.5.5.2 Retrieving a value

The dynvar. val function extracts the value contained in the variable you have named in the argument to this program.

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:

• Parse the assignment string. Notice that in this case I do not surround the var_in value with single quotes. It's not a string expression in this case, but is instead the name of the variable. If you try to use dynvar.val to extract the value of a local, nonpackaged variable, you will receive a compile error.

• Bind the placeholder variable. As with assign, the value I bind is irrelevant, since it actually is an OUT placeholder, about to receive the value from the variable name that is passed in.

• Execute the dynamic PL/SQL block, extract the value into a local variable (whereas in dynvar.assign, it was extracted directly into the variable you provide), and then return the value.

2.5.5.3 Copying a value to a packaged variable

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.

2.5.6.1 Using array processing to insert

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);

In other words: two array binds and one scalar bind. The same value returned by SYSDATE is then applied to all rows inserted.

2.5.6.2 Using array processing to delete

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:

/* Filename on companion disk: 

dyndel.tst */*
DECLARE
   empnos8 DBMS_SQL.NUMBER_TABLE;
   enames8 DBMS_SQL.VARCHAR2_TABLE;
BEGIN
   /* Load up the index-by table. */
   enames8(1) := '%S%';
   enames8(2) := '%I%';

   delemps (enames8);
END;
/

SQL> @dyndel.tst
Rows deleted: 8

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.

NOTE: As you try out these various examples, don't forget to perform ROLLBACKs to restore the data to the original state before continuing or exiting!

2.5.6.3 Using array processing to update

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:

Conditions:

Array placeholder in SET, no placeholders in the WHERE clause.

Behavior:

The value in the last row of the array will be applied to all rows identified by the WHERE clause (or lack of one). All other rows in the array are ignored.

Conditions:

Array placeholder in WHERE clause, no placeholders in SET.

Behavior:

As expected, all rows identified in the WHERE clause have their column values set as determined in the SET statement (all scalar binds or no binds at all).

Conditions:

There are N rows in a SET clause array and M rows in a WHERE clause array, and N is different from M.

Behavior:

The rule of thumb is that smallest number of rows across all arrays are used. So if the SET array has ten rows and the WHERE array has six rows, then only the first six rows of the SET array are used (assuming that both arrays are filled sequentially from same row number).

Conditions:

You use an array in the SET clause. In addition, the WHERE clause uses one or more arrays and is also structured so that each row in the array could identify more than one row of data in the database table (as would be the case with use of a LIKE statement and wildcarded values).

Behavior:

In this situation, for all database records identified by row N in the WHERE array, the values will be set from row N in the SET array.

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.

2.5.6.4 Using array processing to fetch

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;
/

And here are the astounding results from execution of this test script:

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:

1. A cursor for the dynamic SQL. I open the cursor when the package is initialized and keep that cursor allocated for the duration of the session, minimizing memory requirements and maximizing performance.

        c PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;

2. Two arrays to hold the employee IDs and the names. Notice that I must use the special table types provided by DBMS_SQL.

        empno_array DBMS_SQL.NUMBER_TABLE;
        ename_array DBMS_SQL.VARCHAR2_TABLE;

3. A global variable keeping track of the number of rows fetched. I could retrieve this value with the empno.COUNT operator, but this would be misleading if you were appending rows, and it would also entail more overhead than simply storing the value in this variable.

        g_num_fetched PLS_INTEGER := 0;

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:

• I set the starting row for the call to DEFINE_ARRAY according to the append_rows argument. If not appending, I clean out the arrays and start at row 1. If appending, I determine the highest-defined row in both of the arrays and start from the next row.

• If the user does not provide a WHERE clause, I append a trivial "1=1" condition after the WHERE keyword. This is admittedly kludgy and little more than a reflection of this programmer's laziness. The alternative is to do more complex string analysis and concatenation.

• Both calls to DBMS_SQL.DEFINE_ARRAY use the same number of rows and the starting row, ensuring that their contents are correlated properly.

• I execute and fetch with a single line of code. When performing array processing, there is no reason to separate these two steps, unless you will be fetching N number of rows more than once to make sure you have gotten them all.

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:

FUNCTION 

ename_val (row_in IN INTEGER) RETURN emp.ename%TYPE
IS
BEGIN
   IF ename_array.EXISTS (row_in)
   THEN
      RETURN ename_array (row_in);
   ELSE
      RETURN NULL;
   END IF;
END;

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.

2.5.6.5 Using array processing in dynamic PL/SQL

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.

2.5.7.1 RETURNING from a single-row insert

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):

/*Filename on companion disk: 

insret.sp */*
CREATE TABLE note (note_id NUMBER, text VARCHAR2(500));
CREATE UNIQUE INDEX note_text_ind ON note (text);
CREATE SEQUENCE note_seq;

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:

1. Open a cursor and then parse the INSERT statement. Notice that I reference the next value of the sequence right in the VALUES list. The RETURNING clause passes the primary key, note_id, into a placeholder.

2. Bind both the incoming text value and the outgoing primary key. (I am not really binding a value in the second call to DBMS_SQL.BIND_VARIABLE, but I have to perform the bind step anyway.)

3. Execute the cursor and retrieve the primary key from the bind variable in the RETURNING clause. Notice that I pass back the value into a local variable, which is then copied to the OUT argument. Otherwise, I would need to define the note_id_out parameter as an IN OUT parameter.

4. I include an exception section to display any errors that might arise.

The following anonymous block tests this procedure by inserting a row and then trying to insert it again:

/* Filename on companion disk: 

insret.sp */*
DECLARE
   myid note.note_id%TYPE;
BEGIN
   ins_note ('Note1', myid);
   DBMS_OUTPUT.PUT_LINE ('New primary key = ' || myid);
   myid := NULL;
   ins_note ('Note1', myid);
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

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.

2.5.7.2 RETURNING from a multiple-row delete

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.

/* Filename on companion disk: 

delret.sp */*
DECLARE
   v_pkeys DBMS_SQL.NUMBER_TABLE;
BEGIN
   delret ('emp', 'empno', 'deptno = 10', v_pkeys);
   IF v_pkeys.COUNT > 0
   THEN
      FOR ind IN v_pkeys.FIRST .. v_pkeys.LAST
      LOOP
         DBMS_OUTPUT.PUT_LINE ('Deleted ' || v_pkeys(ind));
      END LOOP;
   END IF;
   ROLLBACK;
END;
/

Here are the results from execution of the previous script:

Dynamic Delete Processing:
DELETE FROM emp WHERE deptno = 10 RETURNING empno INTO :idarray
Deleted 7782
Deleted 7839
Deleted 7934