Oracle Bulk Bind

    Bulk bind is a process which is used to improve the performance of the application through collections, i.e... Once we are storing the data in a collection then we are storing. The data in a collection then we are submitting this collection to the SQL all values are modified (0r) deleted (or) inserted at a time only. In bulk bind process, we are using for all statements. Bulk bind is a reverse process of the bulk collect.

Bulk bind Syntax: -  

FORALL variablename in collectionvar.FIRST...Collectionvar.LAST dmlstatements;

Example:-

DECLARE

   TYPE t1 IS VARRAY (10) OF NUMBER (10);

   V_t   t1

            := t1 (20,

                   30,

                   40,

                   50,

                   60,

                   70,

                   80,

                   90);

BEGIN

   FORALL i IN v_t.FIRST .. v_t.LAST

      UPDATE emp

         SET sal = sal + 100

       WHERE deptno = v_t (i);

END;

Bulk collect clauses and also modifies salary of the empno using bulk bind process.

Example:-

DECLARE

   TYPE t1 IS TABLE OF emp.empno%TYPE

                 INDEX BY BINARY_INTEGER;

   V_t   t1;

   CURSOR c1

   IS

      SELECT empno FROM emp;

BEGIN

   OPEN c1;

   FETCH c1

   BULK COLLECT INTO v_t;

   CLOSE c1;

   FORALL I IN v_t.FIRST .. v_t.LAST

      UPDATE emp

         SET sal = sal + 100

       WHERE empno = v_t (i);

END;

    

    Example:-

    

    DECLARE

   TYPE t1 IS TABLE OF emp.empno%TYPE

                 INDEX BY BINARY_INTEGER;

   V_t   t1;

   CURSOR c1

   IS

      SELECT empno FROM emp;

BEGIN

   OPEN c1;

   FETCH c1

   BULK COLLECT INTO v_t;

   CLOSE c1;

   V_t.delete (3);

   FORALL I IN v_t.FIRST .. v_t.LAST

      UPDATE emp

         SET sal = sal + 100

       WHERE empno = v_t (i);

END;

    Error: Element at index(3) does not exist

    

    Note: - before we are applying bulk bind process collections doesn’t have any gaps.to overcome this problem oracle log introduced ‘indices of’ clause in collections

   Solution:-     

   DECLARE

   TYPE t1 IS TABLE OF emp.empno%TYPE

                 INDEX BY BINARY_INTEGER;

   V_t   t1;

   CURSOR c1

   IS

      SELECT empno FROM emp;

BEGIN

   OPEN c1;

   FETCH c1

   BULK COLLECT INTO v_t;

   CLOSE c1;

   V_t.delete (3);

   FORALL I IN INDICES OF v_t

      UPDATE emp

         SET sal = sal + 100

       WHERE empno = v_t (i);

END;

   Sql%bulk_rowcount: - this attribute only used in bulk bind process. This attribute automatically counts, number of records in each iteration.

       Syntax: - sql%bulk_rowcount(index variable)

Example:-

DECLARE

   TYPE t1 IS VARRAY (10) OF NUMBER (10);

   V_t   t1

            := t1 (10,

                   20,

                   30,

                   40,

                   50,

                   60,

                   70);

BEGIN

   FORALL i IN v_t.FIRST .. v_t.LAST

      UPDATE emp

         SET sal = sal + 100

       WHERE deptno = v_t (i);

   FOR I IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.

       put_line (

            'affected number of rows'

         || ' '

         || v_t (i)

         || ' '

         || 'for'

         || '  '

         || SQL%BULK_ROWCOUNT (i));

   END LOOP;

END;

In bulk process, we are using bulk inserts, bulk updates, bulk deletes.

Bulk deletes: -

DECLARE

   TYPE t1 IS VARRAY (10) OF NUMBER (10);

   V_t   t1

            := t1 (20,

                   30,

                   50,

                   60);

BEGIN

   FORALL i IN v_t.FIRST .. v_t.LAST

      DELETE FROM emp

            WHERE deptno = v_t (i);

END;

Prior to Oracle9i there was no way to continue processing after a row-wise exception arises in the bulk binding approach

Oracle9i provides “Save Exceptions” syntax and the corresponding “ORA-24381: error(s) in array DML” exception. This allows the implied loop to continue after row-wise failure

The sql%bulk_exceptions collection allows reporting of the error rows in the exception handler

CREATE TABLE t (text VARCHAR2 (3));

DECLARE

   TYPE words_t IS TABLE OF VARCHAR2 (10);

   words         words_t

                    := words_t ('dog',

                                'fish',

                                'cat',

                                'ball',

                                'bat',

                                'spoke',

                                'pad');

   bulk_errors   EXCEPTION;

   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

BEGIN

   FORALL j IN words.FIRST .. words.LAST SAVE EXCEPTIONS

      INSERT INTO t (text)

           VALUES (words (j));

EXCEPTION

   WHEN bulk_errors

   THEN

      FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT

      LOOP

         DBMS_OUTPUT.Put_Line (

               SQL%BULK_EXCEPTIONS (j).ERROR_INDEX

            || ', '

            || SQLERRM (-SQL%BULK_EXCEPTIONS (j).ERROR_CODE));

      END LOOP;

END;

/

SELECT * FROM t;

 

You May Like:

 

Oracle Bulk Collect

PL/SQL Ref Cursor

PLSQL Record

PL/SQL Bulk Collect And Bulk bind

PL/SQL Index by tables (or) PL/SQL tables (or) associative arrays

PL/SQL Nested tables
PL/SQL Varray