How do I create nested FORALL statements

Question:

Every FORALL statement has to be followed by a DML statement using the index variable. I am facing a situation where a nested FORALL statement is required, such as:
forall indx in collection1.first..collection1.last
forall indx2 in collection2.first..collection2.last
How can I accomplish this goal?

Answer:

The FORALL statement is a truly wondrous addition to the PL/SQL language. The potential gains in performance border on astounding, and it is quite easy to use. It does, however, have some constraints. You really need to think of FORALL as being an "extended DML" statement. In other words, there are actually three FORALL statements:
FORALL INSERT
FORALL DELETE
FORALL UPDATE
From this perspective, "nested" FORALLs don't make much sense. Unfortunately, that doesn't solve your problem, does it? Whenever you work with FORALLs and you need to do something out of the ordinary, you generally end up performing transforms on the collections that drive your FORALLs so that they can be set up to neatly execute the DML. In your case, the "nested" FORALL will have to be resolved into a single collection and single FORALL. Here is an example:
DECLARE
TYPE coll_type1 IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER;

TYPE coll_type2 IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER;

collection1 coll_type1;
collection2 coll_type2;
collection3 coll_type2;
l_row1 PLS_INTEGER;
l_row2 PLS_INTEGER;

PROCEDURE populate_outer_array (coll_inout IN OUT coll_type1)
IS
BEGIN
-- Just a stub
NULL;
END populate_outer_array;

PROCEDURE populate_inner_array (coll_inout IN OUT coll_type2)
IS
BEGIN
-- Just a stub
NULL;
END populate_inner_array;
BEGIN
populate_outer_array (collection1);
populate_inner_array (collection2);
l_row1 := collection1.FIRST;

WHILE (l_row1 IS NOT NULL)
LOOP
l_row2 := collection2.FIRST;

WHILE (l_row2 IS NOT NULL)
LOOP
-- I use values in both collection1 and collection2
-- to produce the value for collection3.
collection3 (collection3.COUNT + 1) :=
collection1 (l_row1)
* collection2 (l_row2);
l_row2 := collection2.NEXT (l_row2);
END LOOP INNER;

l_row1 := collection1.NEXT (l_row1);
END LOOP OUTER;

IF collection3.COUNT > 0
THEN
FORALL indx IN collection3.FIRST .. collection3.LAST
INSERT INTO ... ;
END IF;
END;
/

It's not terribly clever, but it does the job. Depending on the way you need to combine information from these two (or more) collections, you may also be able to take advantage of Oracle Database 10g's new INDICES OF and VALUES OF clauses, which make it easy to use sparse collections to drive FORALL processing.
Finally, Darryl Hurley of Implestrat, who was kind enough to review this month's Q&A, suggested the following as another solution:
"The statement in the FORALL must be DML. One form of DML is INSERT INTO SELECT FROM. The TABLE function allows SELECTs from a function. Why not pass single entries from the first collection to the function along with the whole second collection each time and have it return the amalgamated records? The speed lost by not being able to nest the second FORALL is (ideally) regained by pipelining the function.
"The example below assumes the objective is to process single records from one collection separately against the full second collection. It also assumes that inserts are the desired DML."
SET SERVEROUTPUT ON SIZE 1000000

DROP TYPE one_col_t;

CREATE TYPE one_col_t AS TABLE OF NUMBER;
/

DROP TYPE two_cols_t;
DROP TYPE two_cols_o;

CREATE TYPE two_cols_o AS OBJECT (
col1 NUMBER
,col2 NUMBER
);
/

CREATE TYPE two_cols_t AS TABLE OF two_cols_o;
/

CREATE OR REPLACE FUNCTION extender (p_param NUMBER, p_cols one_col_t)
RETURN two_cols_t PIPELINED
AS
v_ret_val two_cols_o;
BEGIN
FOR counter IN p_cols.FIRST .. p_cols.LAST
LOOP
PIPE ROW (two_cols_o (p_param, p_cols (counter)));
END LOOP;

RETURN;
END;
/

SHO ERR

DECLARE
v_col1 one_col_t := one_col_t ();
v_col2 one_col_t := one_col_t ();
BEGIN
v_col1.EXTEND; v_col1 (1) := 1;
v_col1.EXTEND; v_col1 (2) := 2;
v_col1.EXTEND; v_col1 (3) := 3;
v_col2.EXTEND; v_col2 (1) := 11;
v_col2.EXTEND; v_col2 (2) := 22;
v_col2.EXTEND; v_col2 (3) := 33;
FORALL indx IN v_col1.FIRST .. v_col1.LAST
INSERT INTO my_dual
SELECT *
FROM TABLE (extender (v_col1 (indx), v_col2));
END;
/

SELECT * FROM my_dual
/

ROLLBACK
/