Oracle PL/SQL for DBAs

BULK COLLECT

Oracle8i Database introduced a very powerful new feature that improves the efficiency of queries in PL/SQL: the BULK COLLECT clause . With BULK COLLECT, you can retrieve multiple rows of data through either an implicit or an explicit query with a single roundtrip to and from the database. BULK COLLECT reduces the number of context switches between the PL/SQL and SQL engines and, thereby, reduces the overhead of retrieving data. The syntax for this clause is:

    ... BULK COLLECT INTO collection_name[, collection_name] ...

where collection_name identifies a collection. Here are some rules and restrictions to keep in mind when using BULK COLLECT:

• Prior to Oracle9i Database, you could use BULK COLLECT only with static SQL. With Oracle9i Database and Oracle Database 10g, you can use BULK COLLECT with both dynamic and static SQL.

• You can use BULK COLLECT keywords in any of the following clauses: SELECT INTO, FETCH INTO, and RETURNING INTO.

• The collections you reference can store only scalar values (strings, numbers, dates). In other words, you cannot fetch a row of data into a record structure that is a row in a collection.

• The SQL engine automatically initializes and extends the collections you reference in the BULK COLLECT clause. It starts filling the collections at index 1, inserts elements consecutively (densely), and overwrites the values of any elements that were previously defined.

• You cannot use the SELECT...BULK COLLECT statement in a FORALL statement.

• SELECT...BULK ...