Using Dynamic SQL with Bulk SQL

Using Dynamic SQL with Bulk SQL

Bulk binding lets Oracle bind a variable in a SQL statement to a collection of values. The collection type can be any PL/SQL collection type (index-by table, nested table, or varray). The collection elements must have a SQL datatype such as CHAR, DATE, or NUMBER. Three statements support dynamic bulk binds: EXECUTE IMMEDIATE, FETCH, and FORALL.

EXECUTE IMMEDIATE

You can use the BULK COLLECT INTO clause with the EXECUTE IMMEDIATE statement to store values from each column of a query's result set in a separate collection.

You can use the RETURNING BULK COLLECT INTO clause with the EXECUTE IMMEDIATE statement to store the results of an INSERT, UPDATE, or DELETE statement in a set of collections.

FETCH

You can use the BULK COLLECT INTO clause with the FETCH statement to store values from each column of a cursor in a separate collection.

FORALL

You can put an EXECUTE IMMEDIATE statement with the RETURNING BULK COLLECT INTO inside a FORALL statement. You can store the results of all the INSERT, UPDATE, or DELETE statements in a set of collections.

You can pass subscripted collection elements to the EXECUTE IMMEDIATE statement through the USING clause. You cannot concatenate the subscripted elements directly into the string argument to EXECUTE IMMEDIATE; for example, you cannot build a collection of table names and write a FORALL statement where each iteration applies to a different table.