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 :

3 comments:

  1. Hi There,

    Brilliant article, glad I slogged through the Oracle Bulk Bind it seems that a whole lot of the details really come back to from my past project.

    i creat a view as(see below)
    the problem is that i get dublicat rows and with UNION they are deselected,
    i dont want to use UNION ALL

    so my question is, how can i rewrite the two "select .. " to a Group by expression, Group by: YEAR, Month
    and get the same result as UNION ALL

    CREAT VIEW v_test_rerytering (.......) AS
    select
    F.ALDER
    age,
    cast(P.AR as VARCHAR2(4)) year,
    ccast((P.MANAD)as VARCHAR2(2)) month,
    cast(K.KON_KOD as VARCHAR2(50)) SEX,
    cast('Ext' as VARCHAR2(50)) R_KOD,
    F.NR_REK
    REK
    from test.Table_anstallda F join test.PERIOD P on P.PERIOD =
    F.PERIOD

    union

    select
    F.ALDER
    age,
    cast(P.AR as VARCHAR2(4)) year,
    ccast((P.MANAD)as VARCHAR2(2)) month,
    cast(K.KON_KOD as VARCHAR2(50)) SEX,
    cast('Internal' as VARCHAR2(50)) R_KOD,
    F.NR_dism
    REK
    from test.Table_anstallda F join test.PERIOD P on P.PERIOD_SN =
    F.PERIOD;

    Please keep providing such valuable information.

    Merci Beaucoup,
    Preethi

    ReplyDelete
  2. Ohayo,


    Brilliant article, glad I slogged through the Oracle Bulk Bind it seems that a whole lot of the details really come back to from my past project.


    Please, can you advise me on this query if it is correct? I have been asked to produce a report of vacation by staff for the year 2017 and group by line manager. The question is looking at this query, I have ordered by line manager which I think is showing staff under each manager but I am not sure If I need to break on the line manager and how to accomplish this. Please note that I inherited these table definitions.


    By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).


    Many Thanks,
    Radhey

    ReplyDelete
  3. Hi There,

    What you’re saying is absolutely correct Oracle Bulk Bind , but this isn’t the exact situation everywhere. Where most smart folk work on a project - why can’t you do this the Boss asks :).

    from foll. select, how can i get unique values for screen_type and screen

    select a.EMP_NO,a.SCREEN, b.SCREEN_TYPE
    from EMPLOYEE_TAB a, SCREEN_TAB b
    where a.id = b.ID
    and SCREEN_TYPE like '%S';

    EMP_ID SCREEN SCREEN_TYPE
    EMP_123 SCR100 SCRTYPE100S
    EMP_124 SCR100 SCRTYPE100S
    EMP_125 SCR100 SCRTYPE100S
    EMP_127 SCR102 SCRTYPE102S
    EMP_128 SCR102 SCRTYPE102S
    EMP_135 SCR102 SCRTYPE102S
    EMP_136 SCR102 SCRTYPE102Sv

    THANK YOU!! This saved my butt today, I’m immensely grateful.

    Gracias,
    Preethi.

    ReplyDelete