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.
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.
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.
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.
Bulk SQL passes entire collections back and forth, not just individual elements. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. You can use a single statement instead of a loop that issues a SQL statement in every iteration.
Using the following commands, clauses, and cursor attribute, your applications can construct bulk SQL statements, then execute them dynamically at run time:
- BULK FETCH statement
- BULK EXECUTE IMMEDIATE statement
- FORALL statement
- COLLECT INTO clause
- RETURNING INTO clause
- %BULK_ROWCOUNT cursor attribute
Example of BULK COLLECT INTO Clause
You can bind define variables in a dynamic query using the BULK COLLECT INTO clause, and you can use that clause in a bulk FETCH or bulk EXECUTE IMMEDIATE statement as shown here:
TYPE EmpCurTyp IS REF CURSOR;
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
OPEN emp_cv FOR 'SELECT empno, ename FROM emp';
FETCH emp_cv BULK COLLECT INTO empnos, enames;
EXECUTE IMMEDIATE 'SELECT sal FROM emp'
BULK COLLECT INTO sals;
Related Code Snippets:
- BULK COLLECT - The SQL engine will bulk bind all the values present for a \column in a table befor...