Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page