FORALL 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;

/