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,
[Appendix A] What's on the Companion Disk?
2.5.6 Array Processing with DBMS_SQL 126
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
[Appendix A] What's on the Companion Disk?
2.5.6 Array Processing with DBMS_SQL 127
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
[Appendix A] What's on the Companion Disk?

2.5.6 Array Processing with DBMS_SQL 128
"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);
[Appendix A] What's on the Companion Disk?
2.5.6 Array Processing with DBMS_SQL 129
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.
[Appendix A] What's on the Companion Disk?
2.5.6 Array Processing with DBMS_SQL 130