Bulk Binding Enhancements

Overview

The assigning of values to PL/SQL variables in SQL statements is called binding. The binding of an entire collection at once is called bulk binding. Bulk binds improve performance by minimizing the number of context switches between the PL/SQL and SQL engines. Typically, using bulk binds improves performance for SQL statements that affect four or more database rows. The more rows affected by a SQL statement, the greater the performance gain from bulk binds. Following are the bulk binding enhancements supported by Oracle9i (Oracle 9.0.1 release and later)

• Handling and Reporting Exceptions
• Bulk Binding in Native Dynamic SQL
  • Defining
  • In-Binding
  • Out-Binding

Oracle9i Enhancement For Bulk Fetch From Cursor Variable Assigned By Native Dynamic SQL is described in the section under "Table Functions and Cursor Expressions".

Business Benefits of Bulk Binding Enhancements

• Increased speed and scalability for appropriate applications
• Improved functionality by virtue of better exception handling
  

Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 7-Jan-2003