USING BULK BINDING IN ORACLE

WHAT IS BULK BINDING IN ORACLE:

We know that bulk operation help us to reduce the context switch between SQL engine and PLSQL engine. Bulk operation helps us to fetch all the rows from SQL engine in one go, but when we read the data from collection then we have to loop through the collection. This will create another overhead when we use the data in a DML operation. To reduce the overhead Oracle provides another feature that will complete the DML operation in one go.

So bulk data from database are fetched in one go and then process all the fetched bulk data in one go. So this will drastically improve the performance. This kind of data processing is called bulk binding.

WHAT IS THE DIFFERENCE BETWEEN BULK COLLECT AND BULK BIND IN ORACLE?

For bulk binding oracle provide two features:

BULK COLLECT
FORALL

BULK COLLECT fetches all the data in a single fetch. FORALL processes all the data in a collection in one go.

EXAMPLE 1:

USING RECORD VARIABLE:

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES;
  --
  TYPE EMP_REC IS RECORD
  (EMPLOYEE_ID    EMPLOYEES.EMPLOYEE_ID%TYPE,
   FIRST_NAME     EMPLOYEES.FIRST_NAME%TYPE,
   LAST_NAME      EMPLOYEES.LAST_NAME%TYPE,
   JOB_ID         EMPLOYEES.JOB_ID%TYPE,
   SALARY         EMPLOYEES.SALARY%TYPE
  );
  TYPE T_EMP_TAB IS TABLE OF EMP_REC INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  V_COUNT   NUMBER := 0;
  V_FETCH   NUMBER := 0;
BEGIN
  OPEN EMP_CUR;
  LOOP
    V_FETCH := V_FETCH + 1;
    FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB LIMIT 50;
    EXIT WHEN V_EMP_TAB.COUNT = 0;
    DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN FETCH NO '||V_FETCH||' -> '||V_EMP_TAB.COUNT);
    --
    FORALL I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
    INSERT INTO EMP_LIMIT_TEST VALUES(V_EMP_TAB(I).EMPLOYEE_ID, V_EMP_TAB(I).FIRST_NAME, V_EMP_TAB(I).LAST_NAME, V_EMP_TAB(I).JOB_ID, V_EMP_TAB(I).SALARY);
    SELECT COUNT(1)
    INTO V_COUNT
    FROM EMP_LIMIT_TEST;
    DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN EMP_LIMIT_TEST AFTER FETCH NO '||V_FETCH||' -> '||V_COUNT);
  END LOOP;
END;
/

INDIVIDUAL COLLECTION COLUMNS:

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES;
  --
  TYPE T_EMPLOYEE_ID IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY PLS_INTEGER;
  V_EMPLOYEE_ID T_EMPLOYEE_ID;
  TYPE T_FIRST_NAME IS TABLE OF EMPLOYEES.FIRST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_FIRST_NAME T_FIRST_NAME;
  TYPE T_LAST_NAME IS TABLE OF EMPLOYEES.LAST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_LAST_NAME T_LAST_NAME;
  TYPE T_JOB_ID IS TABLE OF EMPLOYEES.JOB_ID%TYPE INDEX BY PLS_INTEGER;
  V_JOB_ID T_JOB_ID;
  TYPE T_SALARY IS TABLE OF EMPLOYEES.SALARY%TYPE INDEX BY PLS_INTEGER;
  V_SALARY T_SALARY;
  V_COUNT   NUMBER := 0;
  V_FETCH   NUMBER := 0;
BEGIN
  OPEN EMP_CUR;
  LOOP
    V_FETCH := V_FETCH + 1;
    FETCH EMP_CUR BULK COLLECT INTO V_EMPLOYEE_ID,
                                    V_FIRST_NAME,
                                    V_LAST_NAME,
                                    V_JOB_ID,
                                    V_SALARY
    LIMIT 50;
    EXIT WHEN V_EMPLOYEE_ID.COUNT = 0;
    DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN FETCH NO '||V_FETCH||' -> '||V_EMPLOYEE_ID.COUNT);
    --
    FORALL I IN V_EMPLOYEE_ID.FIRST..V_EMPLOYEE_ID.LAST
    INSERT INTO EMP_LIMIT_TEST VALUES(V_EMPLOYEE_ID(I), V_FIRST_NAME(I), V_LAST_NAME(I), V_JOB_ID(I), V_SALARY(I));
    SELECT COUNT(1)
    INTO V_COUNT
    FROM EMP_LIMIT_TEST;
    DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN EMP_LIMIT_TEST AFTER FETCH NO '||V_FETCH||' -> '||V_COUNT);
  END LOOP;
END;
/

TABLE TYPE :

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES;
  --
  TYPE T_EMP_TAB IS TABLE OF EMP_LIMIT_TEST%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  V_COUNT   NUMBER := 0;
  V_FETCH   NUMBER := 0;
BEGIN
  OPEN EMP_CUR;
  LOOP
    V_FETCH := V_FETCH + 1;
    FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB LIMIT 50;
    EXIT WHEN V_EMP_TAB.COUNT = 0;
    DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN FETCH NO '||V_FETCH||' -> '||V_EMP_TAB.COUNT);
    --
    FORALL I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
    INSERT INTO EMP_LIMIT_TEST VALUES(V_EMP_TAB(I).EMPLOYEE_ID, V_EMP_TAB(I).FIRST_NAME, V_EMP_TAB(I).LAST_NAME, V_EMP_TAB(I).JOB_ID, V_EMP_TAB(I).SALARY);
    SELECT COUNT(1)
    INTO V_COUNT
    FROM EMP_LIMIT_TEST;
    DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN EMP_LIMIT_TEST AFTER FETCH NO '||V_FETCH||' -> '||V_COUNT);
  END LOOP;
END;
/

FORALL STATEMENT:

What is the use of FORALL statements:

FEATURES:

FORALL allows to bind the content of a collection into a single DML statement
DML statements are:
- INSERT
- UPDATE
- DELETE
- MERGE
Indexes used inside the FORALL statement are not allowed to refer outside the FORALL statement
Every collection that the DML statement references must have the indexes that matches the values of index. If DELETE, EXTEND or TRIM is applied on one collection then apply it to other collections as well, so that all the collections have the same set of indexes. Otherwise it will raise an PLSQL exception.
If you want to allow FORALL to continue its operation even if the DML statement fails, use SAVE EXCEPTIONS clause. It will store the information about the exception and allow the operation to finish. After completion it will raise a single exception of error code -24381.
For FORALL to be worked fine the collection must be densely filled. If there is a gap between consecutive indexes then oracle raises exception. To avoid this exception oracle introduces INDICES OF and VALUES OF clause to deal with sparse collection.

USING SAVE EXCEPTIONS:

WITHOUT LIMIT CLAUSE & WITHOUT SAVE EXCEPTIONS:

ALTER TABLE EMP_LIMIT_TEST MODIFY(EMPLOYEE_ID NUMBER(6,0) UNIQUE);

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  ORDER BY EMPLOYEE_ID;
  --
  TYPE T_EMP_TAB IS TABLE OF EMP_LIMIT_TEST%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  V_COUNT   NUMBER := 0;
  V_ERROR_CODE VARCHAR2(100);
  V_ERROR_INDEX VARCHAR2(100);
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCH -> '||V_EMP_TAB.COUNT);
  --
  V_EMP_TAB(40).EMPLOYEE_ID := 100; 
  BEGIN
    FORALL I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
    INSERT INTO EMP_LIMIT_TEST VALUES(V_EMP_TAB(I).EMPLOYEE_ID, V_EMP_TAB(I).FIRST_NAME, V_EMP_TAB(I).LAST_NAME, V_EMP_TAB(I).JOB_ID, V_EMP_TAB(I).SALARY);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR ->> '||SQLERRM);
  END;
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMP_LIMIT_TEST;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN EMP_LIMIT_TEST AFTER INSERT '||' -> '||V_COUNT);
END;
/

WITHOUT LIMIT CLAUSE & WITH SAVE EXCEPTIONS:

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  ORDER BY EMPLOYEE_ID;
  --
  TYPE T_EMP_TAB IS TABLE OF EMP_LIMIT_TEST%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  V_COUNT   NUMBER := 0;
  V_ERROR_CODE VARCHAR2(100);
  V_ERROR_INDEX VARCHAR2(100);
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS -> '||V_EMP_TAB.COUNT);
    --
  V_EMP_TAB(40).EMPLOYEE_ID := 100; 
  BEGIN
    FORALL I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST SAVE EXCEPTIONS
    INSERT INTO EMP_LIMIT_TEST VALUES(V_EMP_TAB(I).EMPLOYEE_ID, V_EMP_TAB(I).FIRST_NAME, V_EMP_TAB(I).LAST_NAME, V_EMP_TAB(I).JOB_ID, V_EMP_TAB(I).SALARY);
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -24381 THEN
        FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
        LOOP
          V_ERROR_CODE := SQL%BULK_EXCEPTIONS(I).ERROR_CODE;
          V_ERROR_INDEX := SQL%BULK_EXCEPTIONS(I).ERROR_INDEX;
          IF V_ERROR_CODE IN (22160, 1400) THEN
            NULL;
          ELSE
            DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED AT ROW NO '||V_ERROR_INDEX||'-> '||SQLERRM(-V_ERROR_CODE));
          END IF;
        END LOOP;
      ELSE
        DBMS_OUTPUT.PUT_LINE('ERROR ->> '||SQLERRM);
      END IF;
  END;
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMP_LIMIT_TEST;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN EMP_LIMIT_TEST AFTER INSERT -> '||V_COUNT);
END;
/

SAVE EXCEPTIONS clause allows you to continue to finish processing all the rows even if there was an error occurred at record no 40.

HOW DO I USE BULK COLLECT LIMIT:

WITH LIMIT CLAUSE & WITHOUT SAVE EXCEPTIONS:

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  ORDER BY EMPLOYEE_ID;
  --
  TYPE T_EMP_TAB IS TABLE OF EMP_LIMIT_TEST%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  V_COUNT   NUMBER := 0;
  V_FETCH   NUMBER := 0;
  V_ERROR_CODE VARCHAR2(100);
  V_ERROR_INDEX VARCHAR2(100);
BEGIN
  OPEN EMP_CUR;
  LOOP
    V_FETCH := V_FETCH + 1;
    FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB LIMIT 50;
    EXIT WHEN V_EMP_TAB.COUNT = 0;
    DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN FETCH NO '||V_FETCH||' -> '||V_EMP_TAB.COUNT);
    --
    V_EMP_TAB(40).EMPLOYEE_ID := 100; 
    BEGIN
      FORALL I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
      INSERT INTO EMP_LIMIT_TEST VALUES(V_EMP_TAB(I).EMPLOYEE_ID, V_EMP_TAB(I).FIRST_NAME, V_EMP_TAB(I).LAST_NAME, V_EMP_TAB(I).JOB_ID, V_EMP_TAB(I).SALARY);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR ->> '||SQLERRM);
    END;
    SELECT COUNT(1)
    INTO V_COUNT
    FROM EMP_LIMIT_TEST;
    DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN EMP_LIMIT_TEST AFTER FETCH NO '||V_FETCH||' -> '||V_COUNT);
  END LOOP;
END;
/

You can see that all the records are fetched in 3 phases (50, 50, 8) due to LIMIT clause. In first two phases 40th records raised the error and FORALL processing is stopped after that point. So each time 39 successful records are inserted into the EMP_LIMIT_TEST table. That's why total (39 + 39 + 8) 86 records are inserted into EMP_LIMIT_TEST table.

WITH LIMIT CLAUSE & SAVE EXCEPTIONS:

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  ORDER BY EMPLOYEE_ID;
  --
  TYPE T_EMP_TAB IS TABLE OF EMP_LIMIT_TEST%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  V_COUNT   NUMBER := 0;
  V_FETCH   NUMBER := 0;
  V_ERROR_CODE VARCHAR2(100);
  V_ERROR_INDEX VARCHAR2(100);
BEGIN
  OPEN EMP_CUR;
  LOOP
    V_FETCH := V_FETCH + 1;
    FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB LIMIT 50;
    EXIT WHEN V_EMP_TAB.COUNT = 0;
    DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN FETCH NO '||V_FETCH||' -> '||V_EMP_TAB.COUNT);
    --
    V_EMP_TAB(40).EMPLOYEE_ID := 100; 
    BEGIN
      FORALL I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST SAVE EXCEPTIONS
      INSERT INTO EMP_LIMIT_TEST VALUES(V_EMP_TAB(I).EMPLOYEE_ID, V_EMP_TAB(I).FIRST_NAME, V_EMP_TAB(I).LAST_NAME, V_EMP_TAB(I).JOB_ID, V_EMP_TAB(I).SALARY);
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -24381 THEN
          FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
          LOOP
            V_ERROR_CODE := SQL%BULK_EXCEPTIONS(I).ERROR_CODE;
            V_ERROR_INDEX := SQL%BULK_EXCEPTIONS(I).ERROR_INDEX;
            IF V_ERROR_CODE IN (22160, 1400) THEN
              NULL;
            ELSE
              DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED AT ROW NO '||V_ERROR_INDEX||'-> '||SQLERRM(-V_ERROR_CODE));
            END IF;
          END LOOP;
        ELSE
          DBMS_OUTPUT.PUT_LINE('ERROR ->> '||SQLERRM);
        END IF;
    END;
    SELECT COUNT(1)
    INTO V_COUNT
    FROM EMP_LIMIT_TEST;
    DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN EMP_LIMIT_TEST AFTER FETCH NO '||V_FETCH||' -> '||V_COUNT);
  END LOOP;
END;
/

Using SAVE EXCEPTIONS allows to continue to process all the rows in each fetch except the 40th row. That's why total (49, 49, 8) 106 records are inserted into EMP_LIMIT_TEST table.

CREATING A PROCEDURE WITH BULK COLLECT, FORALL & SAVE EXCEPTIONS:

CREATE TABLE EMP_SAVE_EXCEPTION_TAB
(
 ERROR_ID       NUMBER GENERATED ALWAYS AS IDENTITY,
 ERROR_CODE     VARCHAR2(1000),
 ERROR_MSG      VARCHAR2(4000),
 ERROR_DATE     DATE
);

CREATE OR REPLACE PROCEDURE SP_FORALL_TEST
IS
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  ORDER BY EMPLOYEE_ID;
  --
  TYPE T_EMP_TAB IS TABLE OF EMP_LIMIT_TEST%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  V_COUNT   NUMBER := 0;
  V_FETCH   NUMBER := 0;
  V_ERROR_CODE VARCHAR2(100);
  V_ERROR_INDEX VARCHAR2(100);
  PROCEDURE SP_SAVE_EXCEPTION_PROC
  (
   P_ERROR_CODE     VARCHAR2,
   P_ERROR_MSG      VARCHAR2,
   P_ERROR_DATE     DATE
  )
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO EMP_SAVE_EXCEPTION_TAB(ERROR_CODE, ERROR_MSG, ERROR_DATE)
    VALUES(P_ERROR_CODE, P_ERROR_MSG, P_ERROR_DATE);
    COMMIT;
  END;
  --
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_LIMIT_TEST';
  OPEN EMP_CUR;
  LOOP
    V_FETCH := V_FETCH + 1;
    FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB LIMIT 50;
    EXIT WHEN V_EMP_TAB.COUNT = 0;
    DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN FETCH NO '||V_FETCH||' -> '||V_EMP_TAB.COUNT);
    --
    V_EMP_TAB(40).EMPLOYEE_ID := 100; 
    BEGIN
      FORALL I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST SAVE EXCEPTIONS
      INSERT INTO EMP_LIMIT_TEST VALUES(V_EMP_TAB(I).EMPLOYEE_ID, V_EMP_TAB(I).FIRST_NAME, V_EMP_TAB(I).LAST_NAME, V_EMP_TAB(I).JOB_ID, V_EMP_TAB(I).SALARY);
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -24381 THEN
          FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
          LOOP
            V_ERROR_CODE := SQL%BULK_EXCEPTIONS(I).ERROR_CODE;
            V_ERROR_INDEX := SQL%BULK_EXCEPTIONS(I).ERROR_INDEX;
            IF V_ERROR_CODE IN (22160, 1400) THEN
              NULL;
            ELSE
              SP_SAVE_EXCEPTION_PROC(V_ERROR_CODE, 'ERROR AT INDEX NO -> '||V_ERROR_INDEX||' -> '||SQLERRM(-V_ERROR_CODE), SYSDATE);
            END IF;
          END LOOP;
        ELSE
          SP_SAVE_EXCEPTION_PROC(V_ERROR_CODE, SQLERRM, SYSDATE);
        END IF;
    END;
    SELECT COUNT(1)
    INTO V_COUNT
    FROM EMP_LIMIT_TEST;
    DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN EMP_LIMIT_TEST AFTER FETCH NO '||V_FETCH||' -> '||V_COUNT);
  END LOOP;
EXCEPTION 
  WHEN OTHERS THEN
    SP_SAVE_EXCEPTION_PROC(V_ERROR_CODE, SQLERRM, SYSDATE);
END;
/

EXEC SP_FORALL_TEST;

SELECT * FROM EMP_SAVE_EXCEPTION_TAB;

FORALL & SPARSE COLLECTIONS:

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY 
FROM EMPLOYEES
WHERE UPPER(FIRST_NAME) LIKE UPPER('K%');

SELECT ROW_NUMBER() OVER(ORDER BY EMPLOYEE_ID) SLNO,
EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID;

When sorting the employees data as per EMPLOYEE_ID then first FIRST_NAME with K as initial comes at position 20.

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  ORDER BY EMPLOYEE_ID;
  --
  TYPE T_EMP_TAB IS TABLE OF EMP_LIMIT_TEST%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  V_COUNT   NUMBER := 0;
  V_ERROR_CODE VARCHAR2(100);
  V_ERROR_INDEX VARCHAR2(100);
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS -> '||V_EMP_TAB.COUNT);
    --
  FOR I IN 1..V_EMP_TAB.COUNT
  LOOP
    IF UPPER(V_EMP_TAB(I).FIRST_NAME) LIKE UPPER('K%') THEN
      V_EMP_TAB.DELETE(I);
    END IF;
  END LOOP;
  BEGIN
    FORALL I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
    INSERT INTO EMP_LIMIT_TEST VALUES(V_EMP_TAB(I).EMPLOYEE_ID, V_EMP_TAB(I).FIRST_NAME, V_EMP_TAB(I).LAST_NAME, V_EMP_TAB(I).JOB_ID, V_EMP_TAB(I).SALARY);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR ->> '||SQLERRM);
  END;
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMP_LIMIT_TEST;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN EMP_LIMIT_TEST AFTER INSERT -> '||V_COUNT);
END;
/

As we have deleted the collection it became sparse. So upto consecutive index no its inserted the data into EMP_LIMIT_TEST table. Whenever its find the discontinuity in index no the program raised null pointer exception.

To avoid null pointer exception and dealing with sparse collection while using FORALL, oracle provides two clauses:

INDICES OF CLAUSE
VALUES OF CLAUSE

INDICES OF CLAUSE:

INDICES OF CLAUSE allows us to work with sparse collection by removing the reference to specific elements.
We can specify the upper and lower range by using the BETWEEN clause.

1st EXAMPLE:

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  ORDER BY EMPLOYEE_ID;
  --
  TYPE T_EMP_TAB IS TABLE OF EMP_LIMIT_TEST%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  V_COUNT   NUMBER := 0;
  V_ERROR_CODE VARCHAR2(100);
  V_ERROR_INDEX VARCHAR2(100);
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS -> '||V_EMP_TAB.COUNT);
    --
  FOR I IN 1..V_EMP_TAB.COUNT
  LOOP
    IF UPPER(V_EMP_TAB(I).FIRST_NAME) NOT LIKE UPPER('K%') THEN
      V_EMP_TAB.DELETE(I);
    END IF;
  END LOOP;
  BEGIN
    FORALL I IN INDICES OF V_EMP_TAB
    INSERT INTO EMP_LIMIT_TEST VALUES(V_EMP_TAB(I).EMPLOYEE_ID, V_EMP_TAB(I).FIRST_NAME, V_EMP_TAB(I).LAST_NAME, V_EMP_TAB(I).JOB_ID, V_EMP_TAB(I).SALARY);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR ->> '||SQLERRM);
  END;
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMP_LIMIT_TEST;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN EMP_LIMIT_TEST AFTER INSERT -> '||V_COUNT);
END;
/

SELECT * FROM EMP_LIMIT_TEST;

2nd EXAMPLE : USING BETWEEN CLAUSE:

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  ORDER BY EMPLOYEE_ID;
  --
  TYPE T_EMP_TAB IS TABLE OF EMP_LIMIT_TEST%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  V_COUNT   NUMBER := 0;
  V_ERROR_CODE VARCHAR2(100);
  V_ERROR_INDEX VARCHAR2(100);
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS -> '||V_EMP_TAB.COUNT);
    --
  FOR I IN 1..V_EMP_TAB.COUNT
  LOOP
    IF UPPER(V_EMP_TAB(I).FIRST_NAME) NOT LIKE UPPER('K%') THEN
      V_EMP_TAB.DELETE(I);
    END IF;
  END LOOP;
  BEGIN
    FORALL I IN INDICES OF V_EMP_TAB BETWEEN 1 AND 50
    INSERT INTO EMP_LIMIT_TEST VALUES(V_EMP_TAB(I).EMPLOYEE_ID, V_EMP_TAB(I).FIRST_NAME, V_EMP_TAB(I).LAST_NAME, V_EMP_TAB(I).JOB_ID, V_EMP_TAB(I).SALARY);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR ->> '||SQLERRM);
  END;
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMP_LIMIT_TEST;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN EMP_LIMIT_TEST AFTER INSERT -> '||V_COUNT);
END;
/

3rd EXAMPLE : USING BETWEEN CLAUSE:

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  ORDER BY EMPLOYEE_ID;
  --
  TYPE T_EMP_TAB IS TABLE OF EMP_LIMIT_TEST%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  V_COUNT   NUMBER := 0;
  V_ERROR_CODE VARCHAR2(100);
  V_ERROR_INDEX VARCHAR2(100);
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS -> '||V_EMP_TAB.COUNT);
    --
  FOR I IN 1..V_EMP_TAB.COUNT
  LOOP
    IF UPPER(V_EMP_TAB(I).FIRST_NAME) NOT LIKE UPPER('K%') THEN
      V_EMP_TAB.DELETE(I);
    END IF;
  END LOOP;
  BEGIN
    FORALL I IN INDICES OF V_EMP_TAB BETWEEN 50 AND 108
    INSERT INTO EMP_LIMIT_TEST VALUES(V_EMP_TAB(I).EMPLOYEE_ID, V_EMP_TAB(I).FIRST_NAME, V_EMP_TAB(I).LAST_NAME, V_EMP_TAB(I).JOB_ID, V_EMP_TAB(I).SALARY);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR ->> '||SQLERRM);
  END;
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMP_LIMIT_TEST;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN EMP_LIMIT_TEST AFTER INSERT -> '||V_COUNT);
END;
/

SELECT * FROM EMP_LIMIT_TEST;

VALUES OF CLAUSE:

VALUES OF clause allows the values (not the index) of one collection to be used as the index of another collection.

In the below example we will extract all the Managers of EMPLOYEES table.

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT A.EMPLOYEE_ID,
         A.FIRST_NAME,
         A.LAST_NAME,
         A.JOB_ID,
         A.SALARY,
         A.MANAGER_ID
  FROM EMPLOYEES A
  ORDER BY A.EMPLOYEE_ID;
  --
  TYPE T_EMP_TAB IS TABLE OF EMP_VALUES_OF_TEST%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  TYPE T_MGR_ID IS TABLE OF PLS_INTEGER;
  V_MGR_ID T_MGR_ID := T_MGR_ID();
  V_MANAGER_ID EMPLOYEES.MANAGER_ID%TYPE := NULL;
  V_COUNT   NUMBER := 0;
  V_COUNT_MGR NUMBER := 0;
  V_ERROR_CODE VARCHAR2(100);
  V_ERROR_INDEX VARCHAR2(100);
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS -> '||V_EMP_TAB.COUNT);
  --
  V_COUNT_MGR := 1;
  FOR I IN (SELECT A.EMPLOYEE_ID,
                   A.FIRST_NAME,
                   A.LAST_NAME,
                   A.JOB_ID,
                   A.SALARY,
                   A.MANAGER_ID
            FROM EMPLOYEES A
            WHERE A.EMPLOYEE_ID IN (SELECT B.MANAGER_ID
                                    FROM EMPLOYEES B)
            ORDER BY A.EMPLOYEE_ID)
  LOOP
    V_MGR_ID.EXTEND;
    V_MGR_ID(V_MGR_ID.LAST) := V_COUNT_MGR; 
    V_COUNT_MGR := V_COUNT_MGR + 1;
  END LOOP;
  --
  BEGIN
    FORALL J IN VALUES OF V_MGR_ID
    INSERT INTO EMP_VALUES_OF_TEST VALUES(V_EMP_TAB(J).EMPLOYEE_ID, V_EMP_TAB(J).FIRST_NAME, V_EMP_TAB(J).LAST_NAME, V_EMP_TAB(J).JOB_ID, V_EMP_TAB(J).SALARY, V_EMP_TAB(J).MANAGER_ID);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR ->> '||SQLERRM);
  END;
  --
  V_COUNT := 0;
  --
  SELECT COUNT(1)
  INTO V_COUNT
  FROM EMP_VALUES_OF_TEST;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS IN EMP_LIMIT_TEST AFTER INSERT -> '||V_COUNT);
END;
/

SELECT * FROM EMP_VALUES_OF_TEST;

ADVANTAGES OF BULK BINDING:

Bulk Binding can drastically improve the performance of your PLSQL code as its removed the concept of row by row operations.

PERFORMANCE BENEFITS WITH EXAMPLE:

CREATE OR REPLACE PROCEDURE SP_FORALL_PERFORMANCE_TEST
IS
  CURSOR OBJ_CUR
  IS
  SELECT * FROM ALL_OBJECTS
  ORDER BY OBJECT_ID;
  V_OBJ_CUR OBJ_CUR%ROWTYPE;
  
  TYPE T_ALL_OBJECTS_TAB IS TABLE OF ALL_OBJECTS%ROWTYPE INDEX BY PLS_INTEGER;
  V_ALL_OBJ_TAB T_ALL_OBJECTS_TAB;
  V_START_TIME NUMBER := 0;
  V_END_TIME NUMBER := 0;
  V_ERROR_CODE VARCHAR2(1000);
  V_ERROR_MSG VARCHAR2(4000);
  V_ERROR_INDEX NUMBER := 0;
  
  PROCEDURE SP_SAVE_EXCEPTION_PROC
  (
   P_ERROR_CODE     VARCHAR2,
   P_ERROR_MSG      VARCHAR2,
   P_ERROR_DATE     DATE
  )
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO EMP_SAVE_EXCEPTION_TAB(ERROR_CODE, ERROR_MSG, ERROR_DATE)
    VALUES(P_ERROR_CODE, P_ERROR_MSG, P_ERROR_DATE);
    COMMIT;
  END;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ALL_OBJECTS_FORALL_TEST';
  DBMS_OUTPUT.PUT_LINE('CURSOR FOR LOOP STARTED..');
  V_START_TIME := DBMS_UTILITY.GET_TIME;
  FOR I IN OBJ_CUR
  LOOP
    INSERT INTO ALL_OBJECTS_FORALL_TEST(OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE, ORACLE_MAINTAINED, APPLICATION, DEFAULT_COLLATION, DUPLICATED, SHARDED, CREATED_APPID, CREATED_VSNID, MODIFIED_APPID, MODIFIED_VSNID)
    VALUES(I.OWNER, I.OBJECT_NAME, I.SUBOBJECT_NAME, I.OBJECT_ID, I.DATA_OBJECT_ID, I.OBJECT_TYPE, I.CREATED, I.LAST_DDL_TIME, I.TIMESTAMP, I.STATUS, I.TEMPORARY, I.GENERATED, I.SECONDARY, I.NAMESPACE, I.EDITION_NAME, I.SHARING, I.EDITIONABLE, I.ORACLE_MAINTAINED, I.APPLICATION, I.DEFAULT_COLLATION, I.DUPLICATED, I.SHARDED, I.CREATED_APPID, I.CREATED_VSNID, I.MODIFIED_APPID, I.MODIFIED_VSNID);
  END LOOP;
  V_END_TIME := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('TOTAL TIME TAKEN FOR CURSOR FOR LOOP -> '||(V_END_TIME - V_START_TIME));
  --
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ALL_OBJECTS_FORALL_TEST';
  DBMS_OUTPUT.PUT_LINE('CURSOR OPEN AND FETCH LOOP STARTED..');
  V_START_TIME := DBMS_UTILITY.GET_TIME;
  OPEN OBJ_CUR;
  LOOP
    FETCH OBJ_CUR INTO V_OBJ_CUR;
    EXIT WHEN OBJ_CUR%NOTFOUND;
    INSERT INTO ALL_OBJECTS_FORALL_TEST(OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE, ORACLE_MAINTAINED, APPLICATION, DEFAULT_COLLATION, DUPLICATED, SHARDED, CREATED_APPID, CREATED_VSNID, MODIFIED_APPID, MODIFIED_VSNID)
    VALUES(V_OBJ_CUR.OWNER, V_OBJ_CUR.OBJECT_NAME, V_OBJ_CUR.SUBOBJECT_NAME, V_OBJ_CUR.OBJECT_ID, V_OBJ_CUR.DATA_OBJECT_ID, V_OBJ_CUR.OBJECT_TYPE, V_OBJ_CUR.CREATED, V_OBJ_CUR.LAST_DDL_TIME, V_OBJ_CUR.TIMESTAMP, V_OBJ_CUR.STATUS, V_OBJ_CUR.TEMPORARY, V_OBJ_CUR.GENERATED, V_OBJ_CUR.SECONDARY, V_OBJ_CUR.NAMESPACE, V_OBJ_CUR.EDITION_NAME, V_OBJ_CUR.SHARING, V_OBJ_CUR.EDITIONABLE, V_OBJ_CUR.ORACLE_MAINTAINED, V_OBJ_CUR.APPLICATION, V_OBJ_CUR.DEFAULT_COLLATION, V_OBJ_CUR.DUPLICATED, V_OBJ_CUR.SHARDED, V_OBJ_CUR.CREATED_APPID, V_OBJ_CUR.CREATED_VSNID, V_OBJ_CUR.MODIFIED_APPID, V_OBJ_CUR.MODIFIED_VSNID);
  END LOOP;
  V_END_TIME := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('TOTAL TIME TAKEN FOR CURSOR OPEN AND FETCH LOOP -> '||(V_END_TIME - V_START_TIME));
  CLOSE OBJ_CUR;
  --
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ALL_OBJECTS_FORALL_TEST';
  DBMS_OUTPUT.PUT_LINE('BULK COLLECT AND FOR LOOP STARTED..');
  V_START_TIME := DBMS_UTILITY.GET_TIME;
  OPEN OBJ_CUR;
  FETCH OBJ_CUR BULK COLLECT INTO V_ALL_OBJ_TAB;
  FOR I IN 1..V_ALL_OBJ_TAB.COUNT
  LOOP
    INSERT INTO ALL_OBJECTS_FORALL_TEST(OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE, ORACLE_MAINTAINED, APPLICATION, DEFAULT_COLLATION, DUPLICATED, SHARDED, CREATED_APPID, CREATED_VSNID, MODIFIED_APPID, MODIFIED_VSNID)
    VALUES(V_ALL_OBJ_TAB(I).OWNER, V_ALL_OBJ_TAB(I).OBJECT_NAME, V_ALL_OBJ_TAB(I).SUBOBJECT_NAME, V_ALL_OBJ_TAB(I).OBJECT_ID, V_ALL_OBJ_TAB(I).DATA_OBJECT_ID, V_ALL_OBJ_TAB(I).OBJECT_TYPE, V_ALL_OBJ_TAB(I).CREATED, V_ALL_OBJ_TAB(I).LAST_DDL_TIME, V_ALL_OBJ_TAB(I).TIMESTAMP, V_ALL_OBJ_TAB(I).STATUS, V_ALL_OBJ_TAB(I).TEMPORARY, V_ALL_OBJ_TAB(I).GENERATED, V_ALL_OBJ_TAB(I).SECONDARY, V_ALL_OBJ_TAB(I).NAMESPACE, V_ALL_OBJ_TAB(I).EDITION_NAME, V_ALL_OBJ_TAB(I).SHARING, V_ALL_OBJ_TAB(I).EDITIONABLE, V_ALL_OBJ_TAB(I).ORACLE_MAINTAINED, V_ALL_OBJ_TAB(I).APPLICATION, V_ALL_OBJ_TAB(I).DEFAULT_COLLATION, V_ALL_OBJ_TAB(I).DUPLICATED, V_ALL_OBJ_TAB(I).SHARDED, V_ALL_OBJ_TAB(I).CREATED_APPID, V_ALL_OBJ_TAB(I).CREATED_VSNID, V_ALL_OBJ_TAB(I).MODIFIED_APPID, V_ALL_OBJ_TAB(I).MODIFIED_VSNID);
  END LOOP;
  V_END_TIME := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('TOTAL TIME TAKEN FOR BULK COLLECT AND FOR LOOP -> '||(V_END_TIME - V_START_TIME));
  CLOSE OBJ_CUR;
  --
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ALL_OBJECTS_FORALL_TEST';
  DBMS_OUTPUT.PUT_LINE('BULK COLLECT AND FOR LOOP WITH LIMIT STARTED..');
  V_START_TIME := DBMS_UTILITY.GET_TIME;
  OPEN OBJ_CUR;
  LOOP
    FETCH OBJ_CUR BULK COLLECT INTO V_ALL_OBJ_TAB LIMIT 5000;
    EXIT WHEN V_ALL_OBJ_TAB.COUNT = 0;
    FOR I IN 1..V_ALL_OBJ_TAB.COUNT
    LOOP
      INSERT INTO ALL_OBJECTS_FORALL_TEST(OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE, ORACLE_MAINTAINED, APPLICATION, DEFAULT_COLLATION, DUPLICATED, SHARDED, CREATED_APPID, CREATED_VSNID, MODIFIED_APPID, MODIFIED_VSNID)
      VALUES(V_ALL_OBJ_TAB(I).OWNER, V_ALL_OBJ_TAB(I).OBJECT_NAME, V_ALL_OBJ_TAB(I).SUBOBJECT_NAME, V_ALL_OBJ_TAB(I).OBJECT_ID, V_ALL_OBJ_TAB(I).DATA_OBJECT_ID, V_ALL_OBJ_TAB(I).OBJECT_TYPE, V_ALL_OBJ_TAB(I).CREATED, V_ALL_OBJ_TAB(I).LAST_DDL_TIME, V_ALL_OBJ_TAB(I).TIMESTAMP, V_ALL_OBJ_TAB(I).STATUS, V_ALL_OBJ_TAB(I).TEMPORARY, V_ALL_OBJ_TAB(I).GENERATED, V_ALL_OBJ_TAB(I).SECONDARY, V_ALL_OBJ_TAB(I).NAMESPACE, V_ALL_OBJ_TAB(I).EDITION_NAME, V_ALL_OBJ_TAB(I).SHARING, V_ALL_OBJ_TAB(I).EDITIONABLE, V_ALL_OBJ_TAB(I).ORACLE_MAINTAINED, V_ALL_OBJ_TAB(I).APPLICATION, V_ALL_OBJ_TAB(I).DEFAULT_COLLATION, V_ALL_OBJ_TAB(I).DUPLICATED, V_ALL_OBJ_TAB(I).SHARDED, V_ALL_OBJ_TAB(I).CREATED_APPID, V_ALL_OBJ_TAB(I).CREATED_VSNID, V_ALL_OBJ_TAB(I).MODIFIED_APPID, V_ALL_OBJ_TAB(I).MODIFIED_VSNID);
    END LOOP;
  END LOOP;
  V_END_TIME := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('TOTAL TIME TAKEN FOR BULK COLLECT AND FOR LOOP WITH LIMIT -> '||(V_END_TIME - V_START_TIME));
  CLOSE OBJ_CUR;
  --
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ALL_OBJECTS_FORALL_TEST';
  DBMS_OUTPUT.PUT_LINE('BULK COLLECT AND FORALL STARTED..');
  V_START_TIME := DBMS_UTILITY.GET_TIME;
  OPEN OBJ_CUR;
  FETCH OBJ_CUR BULK COLLECT INTO V_ALL_OBJ_TAB;
  BEGIN
    FORALL I IN 1..V_ALL_OBJ_TAB.COUNT SAVE EXCEPTIONS
    INSERT INTO ALL_OBJECTS_FORALL_TEST(OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE, ORACLE_MAINTAINED, APPLICATION, DEFAULT_COLLATION, DUPLICATED, SHARDED, CREATED_APPID, CREATED_VSNID, MODIFIED_APPID, MODIFIED_VSNID)
    VALUES(V_ALL_OBJ_TAB(I).OWNER, V_ALL_OBJ_TAB(I).OBJECT_NAME, V_ALL_OBJ_TAB(I).SUBOBJECT_NAME, V_ALL_OBJ_TAB(I).OBJECT_ID, V_ALL_OBJ_TAB(I).DATA_OBJECT_ID, V_ALL_OBJ_TAB(I).OBJECT_TYPE, V_ALL_OBJ_TAB(I).CREATED, V_ALL_OBJ_TAB(I).LAST_DDL_TIME, V_ALL_OBJ_TAB(I).TIMESTAMP, V_ALL_OBJ_TAB(I).STATUS, V_ALL_OBJ_TAB(I).TEMPORARY, V_ALL_OBJ_TAB(I).GENERATED, V_ALL_OBJ_TAB(I).SECONDARY, V_ALL_OBJ_TAB(I).NAMESPACE, V_ALL_OBJ_TAB(I).EDITION_NAME, V_ALL_OBJ_TAB(I).SHARING, V_ALL_OBJ_TAB(I).EDITIONABLE, V_ALL_OBJ_TAB(I).ORACLE_MAINTAINED, V_ALL_OBJ_TAB(I).APPLICATION, V_ALL_OBJ_TAB(I).DEFAULT_COLLATION, V_ALL_OBJ_TAB(I).DUPLICATED, V_ALL_OBJ_TAB(I).SHARDED, V_ALL_OBJ_TAB(I).CREATED_APPID, V_ALL_OBJ_TAB(I).CREATED_VSNID, V_ALL_OBJ_TAB(I).MODIFIED_APPID, V_ALL_OBJ_TAB(I).MODIFIED_VSNID);
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -24381 THEN
        FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
        LOOP
          V_ERROR_CODE := SQL%BULK_EXCEPTIONS(I).ERROR_CODE;
          V_ERROR_INDEX := SQL%BULK_EXCEPTIONS(I).ERROR_INDEX;
          IF V_ERROR_CODE IN (22160, 1400) THEN
            NULL;
          ELSE
            SP_SAVE_EXCEPTION_PROC(V_ERROR_CODE, 'ERROR AT INDEX NO -> '||V_ERROR_INDEX||' -> '||SQLERRM(-V_ERROR_CODE), SYSDATE);
          END IF;
        END LOOP;
      ELSE
        SP_SAVE_EXCEPTION_PROC(V_ERROR_CODE, SQLERRM, SYSDATE);
      END IF;
  END;
  V_END_TIME := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('TOTAL TIME TAKEN FOR BULK COLLECT AND FORALL -> '||(V_END_TIME - V_START_TIME));
  CLOSE OBJ_CUR;
  --
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ALL_OBJECTS_FORALL_TEST';
  DBMS_OUTPUT.PUT_LINE('BULK COLLECT AND FORALL WITH LIMIT STARTED..');
  V_START_TIME := DBMS_UTILITY.GET_TIME;
  OPEN OBJ_CUR;
  LOOP
    FETCH OBJ_CUR BULK COLLECT INTO V_ALL_OBJ_TAB LIMIT 5000;
    EXIT WHEN V_ALL_OBJ_TAB.COUNT = 0;
    BEGIN
      FORALL I IN 1..V_ALL_OBJ_TAB.COUNT SAVE EXCEPTIONS
      INSERT INTO ALL_OBJECTS_FORALL_TEST(OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE, ORACLE_MAINTAINED, APPLICATION, DEFAULT_COLLATION, DUPLICATED, SHARDED, CREATED_APPID, CREATED_VSNID, MODIFIED_APPID, MODIFIED_VSNID)
      VALUES(V_ALL_OBJ_TAB(I).OWNER, V_ALL_OBJ_TAB(I).OBJECT_NAME, V_ALL_OBJ_TAB(I).SUBOBJECT_NAME, V_ALL_OBJ_TAB(I).OBJECT_ID, V_ALL_OBJ_TAB(I).DATA_OBJECT_ID, V_ALL_OBJ_TAB(I).OBJECT_TYPE, V_ALL_OBJ_TAB(I).CREATED, V_ALL_OBJ_TAB(I).LAST_DDL_TIME, V_ALL_OBJ_TAB(I).TIMESTAMP, V_ALL_OBJ_TAB(I).STATUS, V_ALL_OBJ_TAB(I).TEMPORARY, V_ALL_OBJ_TAB(I).GENERATED, V_ALL_OBJ_TAB(I).SECONDARY, V_ALL_OBJ_TAB(I).NAMESPACE, V_ALL_OBJ_TAB(I).EDITION_NAME, V_ALL_OBJ_TAB(I).SHARING, V_ALL_OBJ_TAB(I).EDITIONABLE, V_ALL_OBJ_TAB(I).ORACLE_MAINTAINED, V_ALL_OBJ_TAB(I).APPLICATION, V_ALL_OBJ_TAB(I).DEFAULT_COLLATION, V_ALL_OBJ_TAB(I).DUPLICATED, V_ALL_OBJ_TAB(I).SHARDED, V_ALL_OBJ_TAB(I).CREATED_APPID, V_ALL_OBJ_TAB(I).CREATED_VSNID, V_ALL_OBJ_TAB(I).MODIFIED_APPID, V_ALL_OBJ_TAB(I).MODIFIED_VSNID);
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -24381 THEN
          FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
          LOOP
            V_ERROR_CODE := SQL%BULK_EXCEPTIONS(I).ERROR_CODE;
            V_ERROR_INDEX := SQL%BULK_EXCEPTIONS(I).ERROR_INDEX;
            IF V_ERROR_CODE IN (22160, 1400) THEN
              NULL;
            ELSE
              SP_SAVE_EXCEPTION_PROC(V_ERROR_CODE, 'ERROR AT INDEX NO -> '||V_ERROR_INDEX||' -> '||SQLERRM(-V_ERROR_CODE), SYSDATE);
            END IF;
          END LOOP;
        ELSE
          SP_SAVE_EXCEPTION_PROC(V_ERROR_CODE, SQLERRM, SYSDATE);
        END IF;
    END;
  END LOOP;
  V_END_TIME := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('TOTAL TIME TAKEN FOR BULK COLLECT AND FORALL WITH LIMIT -> '||(V_END_TIME - V_START_TIME));
  CLOSE OBJ_CUR;
EXCEPTION
  WHEN OTHERS THEN
    SP_SAVE_EXCEPTION_PROC(V_ERROR_CODE, SQLERRM, SYSDATE);
END;
/

sayan_bappa@yahoo.co.in

View all posts

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

USING BULK BINDING IN ORACLE