Defining

Consider a program to populate elements of a PL/SQL collection from a SELECT query thus…

declare
  type employee_ids_t is table of employees.employee_id%type
    index by binary_integer;

  employee_ids employee_ids_t; n integer:=0;
begin
  for j in ( select employee_id from employees where salary < 3000 )
  loop
    n := n+1; employee_ids(n) := j.employee_id;
  end loop;

end;

Each explicit row by row assignment of the collection element to the cursor component causes a context switch between the PL/SQL engine and the SQL engine resulting in performance overhead. The following formulation (one of a family of constructs generically referred to as bulk binding and available pre-Oracle9i)…

declare
  type employee_ids_t is table of employees.employee_id%type

    index by binary_integer;
  employee_ids employee_ids_t; n integer:=0;
begin
  select employee_id
    bulk collect into employee_ids
    from employees where salary < 3000;
end;

…substantially improves performance by minimizing the number of context switches required to execute the block. (The above fragments work pre-Oracle 9i.)

There are many application implementation situations that require dynamic SQL. Native dynamic SQL (execute immediate and related constructs) is usually preferred over Dbms_Sql because it's easier to write and proof read and executes faster. However, pre-Oracle9i, only Dbms_Sql could be used for dynamic bulk binding. Oracle9i introduces the following syntax for bulk binding in native dynamic SQL …

declare

  type employee_ids_t is table of employees.employee_id%type
    index by binary_integer;
  employee_ids employee_ids_t; n integer:=0;
begin /* new at 9i */
  execute immediate 'select employee_id from employees where salary < 3000'
    bulk collect into employee_ids;
end;