Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Friday, 23 June 2017

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:
 
Share this article :

0 comments:

Post a Comment