Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

USING BULK BINDING IN ORACLE

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;
/
USING BULK BINDING IN ORACLE: Output
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;
/

1 thought on “USING BULK BINDING IN ORACLE”

  1. Pingback: DATETIME AND TIMESTAMP - SayantanBlogonOracle

Leave a Comment

Your email address will not be published.