Bulk bind operations help to improve the performance of PL/SQL operations. Oracle 10g introduces two new types of functionality for bulk binds, including:

• Processing of sparse collections
   
• New exception handling

In earlier versions of Oracle, it was not efficient to process collections that contained sparse element collections. Without the save exceptions clause specified, the statement being executed would terminate when it reached the first deleted element. With save exceptions specified, the operation would not fail, but if there were a number of deleted elements, the performance of the process was much worse than it would have been if save exceptions had not been used.

Oracle 10g introduces the indices keyword, which allows for much faster processing of sparse collections. Performance of statements with the new indices keyword is much better than was possible with the save exceptions clause. Below is an example of this new functionality in use. In this example, there is a table called FAMILIES as the source data. There is a PL/SQL procedure that will remove all dependents from the FAMILIES table, and then populate a table called employee. Since it is likely that there will be a number of dependents for each employee, it is probable that the bulk bind will be very sparse by the time the employees are ready to be inserted, so the example uses the new indices of clause to speed up the process. Here is the example:

CREATE TABLE FAMILIES
( id_ssn          NUMBER,
  Last_name       VARCHAR2(30),
  First_name      VARCHAR2(30),
  Emp_dep_code    NUMBER);

CREATE TABLE employees AS
SELECT * FROM FAMILIES WHERE 1=2;

CREATE UNIQUE INDEX u_employees_idx
on EMPLOYEES(id_ssn);

-- Now, insert some records
INSERT INTO families VALUES (123456780, 'Freeman','Robert',0);
INSERT INTO families VALUES (123456781, 'Freeman','Lisa',1);
INSERT INTO families VALUES (123456782, 'Freeman','Felicia',2);
INSERT INTO families VALUES (123456783, 'Freeman','Sarah',3);
INSERT INTO families VALUES (123456784, 'Freeman','Jacob',4);
INSERT INTO families VALUES (123456785, 'Freeman','Jared',5);
INSERT INTO families VALUES (123456786, 'Freeman','Lizzie',6);
INSERT INTO families VALUES (123456787, 'Bundy','Al',0);
INSERT INTO families VALUES (123456788, 'Bundy','Peg',1);
INSERT INTO families VALUES (123456789, 'Jetson','George',0);
INSERT INTO families VALUES (123456790, 'Jetson','Jane',1);
COMMIT;

-- Now, here is the PL/SQL used to load the employee table.
DECLARE
   TYPE typ_famtyp IS TABLE OF families%ROWTYPE;
   v_fam   typ_famtyp;
BEGIN
   SELECT *
   BULK COLLECT INTO v_fam
   FROM families;
-- loop through the collection and remove
-- unneeded entries.
   FOR rec IN 1..v_fam.LAST()
   LOOP
      IF v_fam(rec).emp_dep_code!=0
      THEN
         v_fam.delete(rec);
      END IF;
   END LOOP;
   FORALL inds IN INDICES OF v_fam
      INSERT INTO employees VALUES v_fam(inds);
END;
/

In Oracle 10g, the PL/SQL engine makes it possible to handle exceptions raised during the execution of a forall statement. This allows code to be written in such a way that an error does not need to halt its execution. It is also possible to save information about the error after it occurs, write it to a table, or display it. The Oracle documentation provides some good examples of this feature.