Bulk Bind in Oracle

Bulk Bind or we can say FORALL in Oracle.
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.

Oracle use two engine to process the PL/SQL code.The PL/SQL engine accepts any valid PL/SQL block or subprogram and SQL engine executes the SQL statements.

Check the below figure how context switch between the PL/SQL and SQL engines.

• PL/SQL engine executes procedural statements and sends SQL statements to the SQL engine.
• SQL engine executes the SQL statements and returns data to the PL/SQL engine(switch between the PL/SQL and SQL engines ).
• If context switch between the PL/SQL and SQL engines is many then it is performance issue.
• That can happen when SQL statements execute inside a loop using collection (index-by table, nested table, varray, or host array) elements as bind variables.

 Check the below example:

DECLARE
TYPE empno_type is table of emp.empno%type
index by pls_integer;
v_emp_no  empno_type;
BEGIN
select empno bulk collect into v_emp_no 
from emp where deptno in (10,20);
for i in v_emp_no.first..v_emp_no.last
loop
update emp set sal=sal+1000 where empno=v_emp_no(i).empno;
commit;
end loop;
END;

in this program UPDATE statement is sent to the SQL engine with each iteration of the FOR loop.

[NB:In such cases, if the SQL statement affects five or more database rows, the use of bulk binds can improve performance considerably.]

Using BULK BIND to improve performance:

• Bulk binds improve performance by minimizing the number of context switches between the PL/SQL and SQL engines. 
• With bulk binds, entire collections(not just individual elements) are passed to the SQL engines for UPDATE the record at once.

Example:

DECLARE
 TYPE empno_type is table of emp.empno%type
 index by pls_integer;
 v_emp_no  empno_type;
BEGIN
 select empno bulk collect into v_emp_no 
 from emp where deptno in (10,20);
 forall i in v_emp_no.first..v_emp_no.last
    update emp set sal=sal+1000 where empno=v_emp_no(i);
commit;

END;

FORALL Statement:

• The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine.

• It allows only DML (INSERT,UPDATE AND DELETE)

• The collection subscript cannot be an expression.

• All collection elements in the specified range must exist. If an element is missing or was deleted, you get an error.

Example:

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10, 20, 30, 40);
BEGIN
   depts.DELETE(3);  -- delete third element
   FORALL i IN depts.FIRST..depts.LAST
      DELETE FROM emp WHERE deptno = depts(i);
      -- raises an "element does not exist" exception
END;

FORALL with SAVE EXCEPTION:

Add SAVE EXCEPTIONS to a FORALL statement, and Oracle Database will save any exceptions that are raised during the execution of individual insert, update, delete, or merge statements generated by the FORALL.

• Using FORALL  i am inserting 100 record into a table, after 50 record failed with error.Then remaining record will not insert into the table. 
• Using FORALL with SAVE EXCEPTION , the error record will ignore( or error record will insert into your error log table) and remaining record will insert .

Create one table call exception_test.
Example:

  DECLARE

  TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

  l_tab          t_tab := t_tab();
  l_error_count  NUMBER;
  
  ex_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last).id := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50).id := NULL;
  l_tab(51).id := NULL;
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
      INSERT INTO exception_test
      VALUES l_tab(i);
  EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i || 
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;
  END;
END;
/