ORA-22160 Error For FORALL
We have
been just amazed at how much better our programs perform when we use FORALL to
do our inserts and updates. We are now building a new application on Oracle
Database 10gRelease 2, and we have run into a problem. In all previous
usages of FORALL, we would generally take a collection that was populated with
a BULK COLLECT and push it into one or more tables.
Now we
have a more complicated scenario, in which we must go through our collection of
"candidate" data for inserts and remove some (perhaps all) of the
rows before doing the insert. When we try to use FORALL, we get this error
message:
ORA-22160: element at index [2750] does
not exist
How can we
avoid this error and get all our data inserted?
I agree
that FORALL is wonderful—one of the most important enhancements to PL/SQL since
Oracle8i was released. And back in Oracle8i and Oracle9i Database,
it is true that the only format with which you could use FORALL was this:
FORALL index_variable
IN low_value .. high_value
<DML_Statement>;
And as in
a "regular" numeric FOR loop, FORALL will iterate through each
integer between low_value and high_value , using that integer to
identify an element in all collections that are bound into the DML statement
with the index_variable . If no element exists at a particular index
value, Oracle Database raises an exception, as you can see in the example in
Listing 3.
Code
Listing 3: Raising ORA-22160
DECLARE
TYPE list_of_names_t IS TABLE OF VARCHAR2(32767)
INDEX BY PLS_INTEGER;
happyfamily list_of_names_t;
BEGIN
happyfamily( 1) := 'Eli';
happyfamily( 2) := 'Chris';
happyfamily( 3) := 'Veva';
happyfamily( 5) := 'Steven';
FORALL indx IN happyfamily.FIRST .. happyfamily.LAST
INSERT INTO first_names
VALUES (happyfamily( indx));
END;
/
DECLARE
*
ERROR at line 1:
ORA-22160: element at index [4] does not
exist
FORALL, in
other words, requires a sequentially or densely filled collection. Now if you
were still running Oracle8i or Oracle9i Database and
wanted to fix this problem, you would have to copy the data from your sparsely
filled collection over to one without any gaps. From a performance standpoint,
this is nothing to worry about; manipulating collections is very fast. But it
does involve writing and maintaining even more code.
In Oracle
Database 10g, Oracle added two new clauses to the FORALL statement: INDICES
OF and VALUES OF. They allow you to avoid the restriction on using densely
filled collections. Instead of using an IN range of values, you can point to a
collection (usually, but not necessarily, the same collection that is
referenced inside the FORALL's DML statement) and say, in effect, "Use
only those index values defined in that other collection" (INDICES OF) or
"Use only index values that are found in the elements of that other
collection" (VALUES OF).
Here is a
rewrite of the code in Listing 3 that avoids the ORA-22160 error (notice
the boldfacedlines):
DECLARE
TYPE list_of_names_t
IS TABLE OF VARCHAR2 (32767)
INDEX BY
PLS_INTEGER;
happyfamily
list_of_names_t;
BEGIN
happyfamily (1) := 'Eli';
happyfamily (2) := 'Chris';
happyfamily (3) := 'Veva';
happyfamily (5) := 'Steven';
FORALL indx
IN INDICES OF happyfamily
INSERT INTO
first_names
VALUES (happyfamily (indx));
END;
/
That is an
example of the simplest way to apply INDICES OF: "self- reference" the
same collection used within the DML statement, to easily avoid errors due to
sparseness in that collection.
Now let's
take a look at VALUES OF. This clause comes in very handy when you want to use
only a subset of the collection to be used within the DML statement.
Suppose,
for example, that I have a procedure that accepts a collection of employee
records and should insert only records for employees with a salary of $10,000
or more. Listing 4 contains the package specification and the body for this
employees_dml program.
Code
Listing 4: Package and body for employees_dml
CREATE OR REPLACE PACKAGE employees_dml
IS
TYPE employees_aat IS TABLE
OF employees%ROWTYPE
INDEX BY
PLS_INTEGER;
PROCEDURE insert_some
(employees_in IN employees_aat);
END employees_dml;
/
CREATE OR REPLACE PACKAGE BODY employees_dml
IS
PROCEDURE insert_some( employees_in IN employees_aat)
IS
TYPE index_values_aat IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER;
l_values_of index_values_aat;
l_index PLS_INTEGER;
BEGIN
l_index := employees_in.FIRST;
WHILE (l_index IS NOT NULL)
LOOP
IF employees_in( l_index).salary >= 10000
THEN
l_values_of( l_values_of.COUNT + 1) := l_index;
END IF;
l_index := employees_in.NEXT( l_index);
END LOOP;
FORALL indx IN VALUES OF l_values_of
INSERT INTO employees
VALUES employees_in( indx);
END insert_some;
END employees_dml;
Lines 5
through 9 in Listing 4 declare the VALUES OF collection, a collection of
PLS_INTEGER values. Then in my WHILE loop (lines 14 through 22), I populate a
row in l_values_of with the index value from employees_in, only if
the salary in that record is at least $10,000.
Thus, when
I get to the FORALL statement (lines 24 through 26), the VALUES OF clause
ensures that all other employee records are ignored.
If you
have the standard Oracle employees table installed with default data, you can
run the script in Listing 5 to verify the behavior of the employees_dml
package.
Code
Listing 5: Verifying behavior of employees_dml package
SELECT COUNT(*)
FROM employees
WHERE salary < 10000
/
DECLARE
l_employees
employees_dml.employees_aat;
BEGIN
SELECT *
BULK COLLECT INTO
l_employees
FROM employees;
DELETE FROM employees;
employees_dml.insert_some
(l_employees);
END;
/
SELECT COUNT(*)
FROM employees
WHERE salary < 10000
/
ROLLBACK
/
COUNT(*)
-------------------------
88
1 row selected.
PL/SQL procedure successfully completed.
COUNT(*)
-------------------------
0
1 row selected.
Rollback complete.
Finally,
you can also use INDICES OF with an entirely different collection that serves
as a kind of filter for the collections used in the DML statement.
Listing 6
shows an example of this approach.
Code
Listing 6: Using INDICES OF as a filter
DECLARE
TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
INDEX BY PLS_INTEGER;
l_employees employee_aat;
TYPE boolean_aat IS TABLE OF BOOLEAN
INDEX BY PLS_INTEGER;
l_employee_indices boolean_aat;
BEGIN
l_employees( 1) := 137;
l_employees( 100) := 126;
l_employees( 500) := 147;
--
l_employee_indices( 1) := FALSE;
l_employee_indices( 500) := TRUE;
l_employee_indices( 799) := NULL;
--
FORALL l_index IN INDICES OF l_employee_indices BETWEEN 1 AND 500
UPDATE employees
SET salary = 10000
WHERE employee_id = l_employees( l_index);
END;
In this
code, I use the index values of defined elements in the l_employee_indices
collection to specify which elements of the l_employees collection to use in my
update statement. Note that on line 21 of Listing 6, I include a BETWEEN clause
to constrain which of the index values of l_employee_indices will be used. So
INDICES OF should fix your problem in this case.
Using Encapsulation Without
Privileges
I have
taken to heart your advice about writing SQL statements (don't write SQL in
application-level code; hide it behind a packaged API, with as much of it
generated as possible). I also decided (and I am a team leader, so my decision
carries some weight) to go the full route, and I revoked privileges on the
tables, so my developers have no choice but to use the encapsulation packages.
Here's my
problem: another of your best practices is to avoid hard-coded declarations and
to anchor variables back to database tables and columns with %TYPE and
%ROWTYPE. But my people can't do that, because I revoked privileges and they
need the SELECT privilege on a table to do that.
What's a
best-practice-oriented guy supposed to do?
So nice to
hear that you are going the encapsulation route! I have used it myself in
recent years in every single one of my development projects, and I really can't
imagine going back to the "old way" of writing all the SQL statements
every time I need them.
And I am
very impressed to hear about your decision to revoke table privileges. That's a
hard thing to do, but once the revocation is in place, it can greatly improve
the robustness of your application.
Yet, as
you point out, it leads to an interesting conflict of best practices.
Here's how
I resolve this problem: I generate three table API packages for each
of my tables:
·
A change package that implements all the basic INSERT, UPDATE, and
DELETE operations
·
A query package that gives me a wide range of functions for
querying data from the table
·
A types package that creates a set of subtypes , which
in essence hide the %TYPE and %ROWTYPE declarations and ensure that I don't
even need SELECT authority on tables to build high-quality code
How does
it work? Simple.
Suppose I
am building an application to maintain a category of things my company sells.
One of my tables is cat_tools, which contains information about tools. The
table has a name, a description, and a universal_id (primary key, defined as a
GUID, or globally unique identifier). Listing 7, includes a portion of the
types package for this table.
Code
Listing 7: Creating a type package as an API
CREATE OR REPLACE PACKAGE cat_tools_tp
IS
SUBTYPE cat_tools_rt IS
cat_tools%ROWTYPE;
SUBTYPE universal_id_t IS
cat_tools.universal_id%TYPE;
SUBTYPE name_t IS
cat_tools.NAME%TYPE;
SUBTYPE description_t IS
cat_tools.description%TYPE;
TYPE table_refcur IS REF
CURSOR
RETURN
cat_tools%ROWTYPE;
TYPE cat_tools_tc IS TABLE
OF cat_tools%ROWTYPE
INDEX BY
PLS_INTEGER;
TYPE universal_id_cc IS
TABLE OF cat_tools.universal_id%TYPE
INDEX BY
BINARY_INTEGER;
END cat_tools_tp;
/
So now
assume that the cat_tools table and both the cat_tools_tp (types) and
cat_tools_qp (query) packages are defined in the CATALOG schema. I grant
EXECUTE on cat_tools_tp and cat_tools_qp to HR. Then from HR, I can write the
block of code in Listing 8.
Code
Listing 8: Access using the type and query packages
DECLARE
/* A string to hold the
assertion name */
l_name
CATALOG.cat_tools_tp.name_t;
/* A collection to
hold the set of tools rows. */
l_tools
CATALOG.cat_tools_tp.cat_tools_tc;
BEGIN
/* The allrows function
retrieves all rows from cat_tools. */
l_tools :=
CATALOG.cat_tools_qp.allrows;
/* Assign each name from
collection to local variable. */
FOR indx IN 1 ..
l_tools.COUNT
LOOP
l_name :=
l_tools (indx).NAME;
END LOOP;
END;
/
As you can
see, even though the HR schema has no access to the cat_tools table, I am able
to declare variables by using datatypes that are anchored (indirectly) to the
table and its columns. I can also query the contents of the table.
And if the
table ever changes, the types package will also change (that is, you will
regenerate it). All programs that reference this package will then have to be
recompiled; thus, you have the same dependencies you would have with %ROWTYPE
and %TYPE declarations.
