Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

USING BULK OPERATION IN PLSQL

Leave a Comment / COLLECTIONS, ADVANCED PLSQL / By

USING BULK OPERATION IN PLSQL:

INTRODUCTION:

  • All PLSQL developers mostly use both PLSQL and SQL statements in a PLSQL program.
  • There are two engines used by oracle to process PLSQL statements.
  • Procedural statements are handled by PLSQL Engine
  • SQL statements are processed by SQL engine
  • When PLSQL engine finds any SQL statement its stopped and send the SQL statement to SQL engine. SQL engine then processes the SQL statement and returns the result to PLSQL engine. This process is called Context Switch.
  • Whenever a context switch is happened it creates an overhead and reduce the performance of the program.
  • Context Switch creates most performance problem when developer use a cursor and process the data row by row via a loop.
  • To reduce the context switch effect oracle introduced Bulk operation in PLSQL.
  • Bulk operation helps us to fetch multiple rows in a single fetch. So that the use of loop to fetch data can be avoided which in turn increases the performance.

For bulk operation oracle provided BULK COLLECT clause.

USAGE OF BULK COLLECT CLAUSE:

  • SELECT INTO STATEMENT
  • FETCH STATEMENT
  • RETURNING INTO STATEMENT OF
    • INSERT
    • UPDATE
    • DELETE
    • EXECUTE IMMEDIATE

FEATURES OF BULK COLLECT:

  • It can be used with all three types of collections:
    • ASSOCIATIVE ARRAYS
    • NESTED TABLES
    • VARRAYS
  • You can fetch the columns in the SELECT list in each individual collections or in a single collection of records.
  • Collections are always densely populated, starting with index value 1.
  • If no row is fetched then the collection is emptied all the elements.
  • It is auto initialized the collection, so for NESTED TABLE and VARRAYS, there is no need to initialize the collection variable.

EXAMPLE OF SELECT INTO STATEMENT:

EXAMPLE 1:

ASSOCIATED ARRAYS:
DECLARE
  TYPE T_EMP_TAB IS TABLE OF EMPLOYEES%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  SELECT * BULK COLLECT INTO V_EMP_TAB
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/
USING BULK OPERATION IN PLSQL: Output
NESTED TABLES:
DECLARE
  TYPE T_EMP_TAB IS TABLE OF EMPLOYEES%ROWTYPE;
  V_EMP_TAB T_EMP_TAB := T_EMP_TAB();
BEGIN
  SELECT * BULK COLLECT INTO V_EMP_TAB
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/
WITHOUT INITIALIZING THE NESTED TABLE:
DECLARE
  TYPE T_EMP_TAB IS TABLE OF EMPLOYEES%ROWTYPE;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  SELECT * BULK COLLECT INTO V_EMP_TAB
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/
VARRAYS:
DECLARE
  TYPE T_EMP_TAB IS VARRAY(6) OF EMPLOYEES%ROWTYPE;
  V_EMP_TAB T_EMP_TAB := T_EMP_TAB();
BEGIN
  SELECT * BULK COLLECT INTO V_EMP_TAB
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/
WITHOUT INITIALIZING THE VARRAYS:
DECLARE
  TYPE T_EMP_TAB IS VARRAY(6) OF EMPLOYEES%ROWTYPE;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  SELECT * BULK COLLECT INTO V_EMP_TAB
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/
EXAMPLE 2:

EXAMPLE OF SELECT INTO STATEMENT WITH COLUMN AS COLLECTION TABLE:

ASSOCIATIVE ARRAYS:
DECLARE
  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;
BEGIN
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID
  BULK COLLECT INTO V_EMPLOYEE_ID,
                    V_FIRST_NAME,
                    V_LAST_NAME,
                    V_JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMPLOYEE_ID.COUNT);
  FOR I IN 1..V_EMPLOYEE_ID.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMPLOYEE_ID(I)||' NAME -> '||V_FIRST_NAME(I)||' '||V_LAST_NAME(I)||' JOBID -> '||V_JOB_ID(I));
  END LOOP;
END;
/
NESTED TABLES:
DECLARE
  TYPE T_EMPLOYEE_ID IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE;
  V_EMPLOYEE_ID T_EMPLOYEE_ID;
  TYPE T_FIRST_NAME IS TABLE OF EMPLOYEES.FIRST_NAME%TYPE;
  V_FIRST_NAME T_FIRST_NAME;
  TYPE T_LAST_NAME IS TABLE OF EMPLOYEES.LAST_NAME%TYPE;
  V_LAST_NAME T_LAST_NAME;
  TYPE T_JOB_ID IS TABLE OF EMPLOYEES.JOB_ID%TYPE;
  V_JOB_ID T_JOB_ID;
BEGIN
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID
  BULK COLLECT INTO V_EMPLOYEE_ID,
                    V_FIRST_NAME,
                    V_LAST_NAME,
                    V_JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMPLOYEE_ID.COUNT);
  FOR I IN 1..V_EMPLOYEE_ID.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMPLOYEE_ID(I)||' NAME -> '||V_FIRST_NAME(I)||' '||V_LAST_NAME(I)||' JOBID -> '||V_JOB_ID(I));
  END LOOP;
END;
/
VARRAYS:
DECLARE
  TYPE T_EMPLOYEE_ID IS VARRAY(6) OF EMPLOYEES.EMPLOYEE_ID%TYPE;
  V_EMPLOYEE_ID T_EMPLOYEE_ID;
  TYPE T_FIRST_NAME IS VARRAY(6) OF EMPLOYEES.FIRST_NAME%TYPE;
  V_FIRST_NAME T_FIRST_NAME;
  TYPE T_LAST_NAME IS VARRAY(6) OF EMPLOYEES.LAST_NAME%TYPE;
  V_LAST_NAME T_LAST_NAME;
  TYPE T_JOB_ID IS VARRAY(6) OF EMPLOYEES.JOB_ID%TYPE;
  V_JOB_ID T_JOB_ID;
BEGIN
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID
  BULK COLLECT INTO V_EMPLOYEE_ID,
                    V_FIRST_NAME,
                    V_LAST_NAME,
                    V_JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMPLOYEE_ID.COUNT);
  FOR I IN 1..V_EMPLOYEE_ID.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMPLOYEE_ID(I)||' NAME -> '||V_FIRST_NAME(I)||' '||V_LAST_NAME(I)||' JOBID -> '||V_JOB_ID(I));
  END LOOP;
END;
/
EXAMPLE 3:

EXAMPLE OF SELECT INTO STATEMENT WITH TABLE OF USER DEFINED RECORD TYPE:

ASSOCIATIVE ARRAYS:
DECLARE
  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
  );
  TYPE T_EMP_TAB IS TABLE OF EMP_REC INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID
  BULK COLLECT INTO V_EMP_TAB
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/
NESTED TABLES:
DECLARE
  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
  );
  TYPE T_EMP_TAB IS TABLE OF EMP_REC;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID
  BULK COLLECT INTO V_EMP_TAB
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/
VARRAYS:
DECLARE
  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
  );
  TYPE T_EMP_TAB IS VARRAY(6) OF EMP_REC;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID
  BULK COLLECT INTO V_EMP_TAB
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/

EXAMPLE OF FETCH STATEMENT:

EXAMPLE 1:
ASSOCIATIVE ARRAYS:
DECLARE
  CURSOR EMP_CUR
  IS
  SELECT * 
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  TYPE T_EMP_TAB IS TABLE OF EMP_CUR%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/
NESTED TABLES:
DECLARE
  CURSOR EMP_CUR
  IS
  SELECT * 
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  TYPE T_EMP_TAB IS TABLE OF EMP_CUR%ROWTYPE;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/
VARRAYS:
DECLARE
  CURSOR EMP_CUR
  IS
  SELECT * 
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  TYPE T_EMP_TAB IS VARRAY(6) OF EMP_CUR%ROWTYPE;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/
EXAMPLE 2:

EXAMPLE OF FETCH STATEMENT WITH COLUMN AS COLLECTION TABLE:

ASSOCIATIVE ARRAYS:
DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  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;
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR
  BULK COLLECT INTO V_EMPLOYEE_ID,
                    V_FIRST_NAME,
                    V_LAST_NAME,
                    V_JOB_ID;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMPLOYEE_ID.COUNT);
  FOR I IN 1..V_EMPLOYEE_ID.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMPLOYEE_ID(I)||' NAME -> '||V_FIRST_NAME(I)||' '||V_LAST_NAME(I)||' JOBID -> '||V_JOB_ID(I));
  END LOOP;
END;
/
NESTED TABLES:
DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  TYPE T_EMPLOYEE_ID IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE;
  V_EMPLOYEE_ID T_EMPLOYEE_ID;
  TYPE T_FIRST_NAME IS TABLE OF EMPLOYEES.FIRST_NAME%TYPE;
  V_FIRST_NAME T_FIRST_NAME;
  TYPE T_LAST_NAME IS TABLE OF EMPLOYEES.LAST_NAME%TYPE;
  V_LAST_NAME T_LAST_NAME;
  TYPE T_JOB_ID IS TABLE OF EMPLOYEES.JOB_ID%TYPE;
  V_JOB_ID T_JOB_ID;
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR
  BULK COLLECT INTO V_EMPLOYEE_ID,
                    V_FIRST_NAME,
                    V_LAST_NAME,
                    V_JOB_ID;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMPLOYEE_ID.COUNT);
  FOR I IN 1..V_EMPLOYEE_ID.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMPLOYEE_ID(I)||' NAME -> '||V_FIRST_NAME(I)||' '||V_LAST_NAME(I)||' JOBID -> '||V_JOB_ID(I));
  END LOOP;
END;
/
VARRAYS:
DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  TYPE T_EMPLOYEE_ID IS VARRAY(6) OF EMPLOYEES.EMPLOYEE_ID%TYPE;
  V_EMPLOYEE_ID T_EMPLOYEE_ID;
  TYPE T_FIRST_NAME IS VARRAY(6) OF EMPLOYEES.FIRST_NAME%TYPE;
  V_FIRST_NAME T_FIRST_NAME;
  TYPE T_LAST_NAME IS VARRAY(6) OF EMPLOYEES.LAST_NAME%TYPE;
  V_LAST_NAME T_LAST_NAME;
  TYPE T_JOB_ID IS VARRAY(6) OF EMPLOYEES.JOB_ID%TYPE;
  V_JOB_ID T_JOB_ID;
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR
  BULK COLLECT INTO V_EMPLOYEE_ID,
                    V_FIRST_NAME,
                    V_LAST_NAME,
                    V_JOB_ID;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMPLOYEE_ID.COUNT);
  FOR I IN 1..V_EMPLOYEE_ID.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMPLOYEE_ID(I)||' NAME -> '||V_FIRST_NAME(I)||' '||V_LAST_NAME(I)||' JOBID -> '||V_JOB_ID(I));
  END LOOP;
END;
/
EXAMPLE 3:

EXAMPLE OF FETCH STATEMENT WITH TABLE OF USER DEFINED RECORD TYPE:

ASSOCIATIVE ARRAYS:
DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  --
  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
  );
  TYPE T_EMP_TAB IS TABLE OF EMP_REC INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/
NESTED TABLES:
DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  --
  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
  );
  TYPE T_EMP_TAB IS TABLE OF EMP_REC;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/
VARRAYS:
DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  --
  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
  );
  TYPE T_EMP_TAB IS VARRAY(6) OF EMP_REC;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' JOBID -> '||V_EMP_TAB(I).JOB_ID);
  END LOOP;
END;
/

EXAMPLE OF RETURNING INTO STATEMENT(WITH ASSOCIATIVE ARRAYS):

INSERT:
CREATE TABLE EMP_RETURN_TAB
AS
SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID
FROM EMPLOYEES
WHERE 1 = 2;
DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  --
  TYPE T_EMPLOYEE_ID IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY PLS_INTEGER;
  V_EMPLOYEE_ID T_EMPLOYEE_ID;
  V_EMPLOYEE_ID_RET T_EMPLOYEE_ID;
  TYPE T_FIRST_NAME IS TABLE OF EMPLOYEES.FIRST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_FIRST_NAME T_FIRST_NAME;
  V_FIRST_NAME_RET T_FIRST_NAME;
  TYPE T_LAST_NAME IS TABLE OF EMPLOYEES.LAST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_LAST_NAME T_LAST_NAME;
  V_LAST_NAME_RET T_LAST_NAME;
  TYPE T_JOB_ID IS TABLE OF EMPLOYEES.JOB_ID%TYPE INDEX BY PLS_INTEGER;
  V_JOB_ID T_JOB_ID;
  V_JOB_ID_RET T_JOB_ID;
  TYPE T_SALARY IS TABLE OF EMPLOYEES.SALARY%TYPE INDEX BY PLS_INTEGER;
  V_SALARY T_SALARY;
  V_SALARY_RET T_SALARY;
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR
  BULK COLLECT INTO V_EMPLOYEE_ID,
                    V_FIRST_NAME,
                    V_LAST_NAME,
                    V_JOB_ID,
                    V_SALARY;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMPLOYEE_ID.COUNT);
  FOR I IN 1..V_EMPLOYEE_ID.COUNT
  LOOP
    INSERT INTO EMP_RETURN_TAB VALUES(V_EMPLOYEE_ID(I),V_FIRST_NAME(I),V_LAST_NAME(I),V_JOB_ID(I), V_SALARY(I))
    RETURNING EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY INTO V_EMPLOYEE_ID_RET(I), V_FIRST_NAME_RET(I), V_LAST_NAME_RET(I), V_JOB_ID_RET(I), V_SALARY_RET(I);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS INSERTED -> '||V_EMPLOYEE_ID_RET.COUNT);
  FOR I IN V_EMPLOYEE_ID_RET.FIRST..V_EMPLOYEE_ID_RET.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMPLOYEE_ID_RET(I)||', NAME -> '||V_FIRST_NAME_RET(I)||' '||V_LAST_NAME_RET(I)||', JOBID -> '||V_JOB_ID_RET(I)||', SALARY -> '||V_SALARY_RET(I));
  END LOOP;
END;
/
UPDATE:
SELECT * FROM EMP_RETURN_TAB;
DECLARE
  TYPE T_EMPLOYEE_ID IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY PLS_INTEGER;
  V_EMPLOYEE_ID_RET T_EMPLOYEE_ID;
  TYPE T_FIRST_NAME IS TABLE OF EMPLOYEES.FIRST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_FIRST_NAME_RET T_FIRST_NAME;
  TYPE T_LAST_NAME IS TABLE OF EMPLOYEES.LAST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_LAST_NAME_RET T_LAST_NAME;
  TYPE T_JOB_ID IS TABLE OF EMPLOYEES.JOB_ID%TYPE INDEX BY PLS_INTEGER;
  V_JOB_ID_RET T_JOB_ID;
  TYPE T_SALARY IS TABLE OF EMPLOYEES.SALARY%TYPE INDEX BY PLS_INTEGER;
  V_SALARY_RET T_SALARY;
BEGIN
  UPDATE EMP_RETURN_TAB
  SET SALARY = SALARY * 1.1
  RETURNING EMPLOYEE_ID,
            FIRST_NAME,
            LAST_NAME,
            JOB_ID,
            SALARY
  BULK COLLECT INTO V_EMPLOYEE_ID_RET,
                    V_FIRST_NAME_RET,
                    V_LAST_NAME_RET,
                    V_JOB_ID_RET,
                    V_SALARY_RET;
  --
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS UPDATED -> '||V_EMPLOYEE_ID_RET.COUNT);
  FOR I IN V_EMPLOYEE_ID_RET.FIRST..V_EMPLOYEE_ID_RET.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMPLOYEE_ID_RET(I)||', NAME -> '||V_FIRST_NAME_RET(I)||' '||V_LAST_NAME_RET(I)||', JOBID -> '||V_JOB_ID_RET(I)||', SALARY -> '||V_SALARY_RET(I));
  END LOOP;
END;
/
DELETE:
DECLARE
  TYPE T_EMPLOYEE_ID IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY PLS_INTEGER;
  V_EMPLOYEE_ID_RET T_EMPLOYEE_ID;
  TYPE T_FIRST_NAME IS TABLE OF EMPLOYEES.FIRST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_FIRST_NAME_RET T_FIRST_NAME;
  TYPE T_LAST_NAME IS TABLE OF EMPLOYEES.LAST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_LAST_NAME_RET T_LAST_NAME;
  TYPE T_JOB_ID IS TABLE OF EMPLOYEES.JOB_ID%TYPE INDEX BY PLS_INTEGER;
  V_JOB_ID_RET T_JOB_ID;
  TYPE T_SALARY IS TABLE OF EMPLOYEES.SALARY%TYPE INDEX BY PLS_INTEGER;
  V_SALARY_RET T_SALARY;
BEGIN
  DELETE EMP_RETURN_TAB
  RETURNING EMPLOYEE_ID,
            FIRST_NAME,
            LAST_NAME,
            JOB_ID,
            SALARY
  BULK COLLECT INTO V_EMPLOYEE_ID_RET,
                    V_FIRST_NAME_RET,
                    V_LAST_NAME_RET,
                    V_JOB_ID_RET,
                    V_SALARY_RET;
  --
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS DELETED -> '||V_EMPLOYEE_ID_RET.COUNT);
  FOR I IN V_EMPLOYEE_ID_RET.FIRST..V_EMPLOYEE_ID_RET.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMPLOYEE_ID_RET(I)||', NAME -> '||V_FIRST_NAME_RET(I)||' '||V_LAST_NAME_RET(I)||', JOBID -> '||V_JOB_ID_RET(I)||', SALARY -> '||V_SALARY_RET(I));
  END LOOP;
END;
/

EXECUTE IMMEDIATE:

EXAMPLE 1:
INSERT:
DECLARE
  TYPE T_EMPLOYEE_ID IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY PLS_INTEGER;
  V_EMPLOYEE_ID T_EMPLOYEE_ID;
  V_EMPLOYEE_ID_RET T_EMPLOYEE_ID;
  TYPE T_FIRST_NAME IS TABLE OF EMPLOYEES.FIRST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_FIRST_NAME T_FIRST_NAME;
  V_FIRST_NAME_RET T_FIRST_NAME;
  TYPE T_LAST_NAME IS TABLE OF EMPLOYEES.LAST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_LAST_NAME T_LAST_NAME;
  V_LAST_NAME_RET T_LAST_NAME;
  TYPE T_JOB_ID IS TABLE OF EMPLOYEES.JOB_ID%TYPE INDEX BY PLS_INTEGER;
  V_JOB_ID T_JOB_ID;
  V_JOB_ID_RET T_JOB_ID;
  TYPE T_SALARY IS TABLE OF EMPLOYEES.SALARY%TYPE INDEX BY PLS_INTEGER;
  V_SALARY T_SALARY;
  V_SALARY_RET T_SALARY;
BEGIN
  EXECUTE IMMEDIATE 'SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = 30' 
  BULK COLLECT INTO V_EMPLOYEE_ID, V_FIRST_NAME, V_LAST_NAME, V_JOB_ID, V_SALARY;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMPLOYEE_ID.COUNT);
  FOR I IN 1..V_EMPLOYEE_ID.COUNT
  LOOP
    INSERT INTO EMP_RETURN_TAB VALUES(V_EMPLOYEE_ID(I),V_FIRST_NAME(I),V_LAST_NAME(I),V_JOB_ID(I), V_SALARY(I))
    RETURNING EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY INTO V_EMPLOYEE_ID_RET(I), V_FIRST_NAME_RET(I), V_LAST_NAME_RET(I), V_JOB_ID_RET(I), V_SALARY_RET(I);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS INSERTED -> '||V_EMPLOYEE_ID_RET.COUNT);
  FOR I IN V_EMPLOYEE_ID_RET.FIRST..V_EMPLOYEE_ID_RET.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMPLOYEE_ID_RET(I)||', NAME -> '||V_FIRST_NAME_RET(I)||' '||V_LAST_NAME_RET(I)||', JOBID -> '||V_JOB_ID_RET(I)||', SALARY -> '||V_SALARY_RET(I));
  END LOOP;
END;
/
UPDATE:
SELECT * FROM EMP_RETURN_TAB;
DECLARE
  TYPE T_EMPLOYEE_ID IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY PLS_INTEGER;
  V_EMPLOYEE_ID_RET T_EMPLOYEE_ID;
  TYPE T_FIRST_NAME IS TABLE OF EMPLOYEES.FIRST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_FIRST_NAME_RET T_FIRST_NAME;
  TYPE T_LAST_NAME IS TABLE OF EMPLOYEES.LAST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_LAST_NAME_RET T_LAST_NAME;
  TYPE T_JOB_ID IS TABLE OF EMPLOYEES.JOB_ID%TYPE INDEX BY PLS_INTEGER;
  V_JOB_ID_RET T_JOB_ID;
  TYPE T_SALARY IS TABLE OF EMPLOYEES.SALARY%TYPE INDEX BY PLS_INTEGER;
  V_SALARY_RET T_SALARY;
BEGIN
  EXECUTE IMMEDIATE 'UPDATE EMP_RETURN_TAB
                     SET SALARY = SALARY * 1.1
                     RETURNING EMPLOYEE_ID,
                               FIRST_NAME,
                               LAST_NAME,
                               JOB_ID,
                               SALARY
                     INTO :V_EMPLOYEE_ID_RET,
                          :V_FIRST_NAME_RET,
                          :V_LAST_NAME_RET,
                          :V_JOB_ID_RET,
                          :V_SALARY_RET'
  RETURNING BULK COLLECT INTO V_EMPLOYEE_ID_RET,
                              V_FIRST_NAME_RET,
                              V_LAST_NAME_RET,
                              V_JOB_ID_RET,
                              V_SALARY_RET;
  --
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS UPDATED -> '||V_EMPLOYEE_ID_RET.COUNT);
  FOR I IN V_EMPLOYEE_ID_RET.FIRST..V_EMPLOYEE_ID_RET.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMPLOYEE_ID_RET(I)||', NAME -> '||V_FIRST_NAME_RET(I)||' '||V_LAST_NAME_RET(I)||', JOBID -> '||V_JOB_ID_RET(I)||', SALARY -> '||V_SALARY_RET(I));
  END LOOP;
END;
/

DELETE:
DECLARE
  TYPE T_EMPLOYEE_ID IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY PLS_INTEGER;
  V_EMPLOYEE_ID_RET T_EMPLOYEE_ID;
  TYPE T_FIRST_NAME IS TABLE OF EMPLOYEES.FIRST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_FIRST_NAME_RET T_FIRST_NAME;
  TYPE T_LAST_NAME IS TABLE OF EMPLOYEES.LAST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_LAST_NAME_RET T_LAST_NAME;
  TYPE T_JOB_ID IS TABLE OF EMPLOYEES.JOB_ID%TYPE INDEX BY PLS_INTEGER;
  V_JOB_ID_RET T_JOB_ID;
  TYPE T_SALARY IS TABLE OF EMPLOYEES.SALARY%TYPE INDEX BY PLS_INTEGER;
  V_SALARY_RET T_SALARY;
BEGIN
  EXECUTE IMMEDIATE 'DELETE EMP_RETURN_TAB
                     RETURNING EMPLOYEE_ID,
                               FIRST_NAME,
                               LAST_NAME,
                               JOB_ID,
                               SALARY
                     INTO :V_EMPLOYEE_ID_RET,
                          :V_FIRST_NAME_RET,
                          :V_LAST_NAME_RET,
                          :V_JOB_ID_RET,
                          :V_SALARY_RET'
  RETURNING BULK COLLECT INTO V_EMPLOYEE_ID_RET,
                              V_FIRST_NAME_RET,
                              V_LAST_NAME_RET,
                              V_JOB_ID_RET,
                              V_SALARY_RET;
  --
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS DELETED -> '||V_EMPLOYEE_ID_RET.COUNT);
  FOR I IN V_EMPLOYEE_ID_RET.FIRST..V_EMPLOYEE_ID_RET.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMPLOYEE_ID_RET(I)||', NAME -> '||V_FIRST_NAME_RET(I)||' '||V_LAST_NAME_RET(I)||', JOBID -> '||V_JOB_ID_RET(I)||', SALARY -> '||V_SALARY_RET(I));
  END LOOP;
END;
/

EXAMPLE 2 (WITH ASSOCIATIVE ARRAYS):

INSERT:
DECLARE
  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_EMP_TAB_RET T_EMP_TAB;
BEGIN
  EXECUTE IMMEDIATE 'SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = 30' 
  BULK COLLECT INTO V_EMP_TAB;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS FETCHED -> '||V_EMP_TAB.COUNT);
  FOR I IN 1..V_EMP_TAB.COUNT
  LOOP
    INSERT INTO EMP_RETURN_TAB 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)
    RETURNING EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY INTO V_EMP_TAB_RET(I);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS INSERTED -> '||V_EMP_TAB_RET.COUNT);
  FOR I IN V_EMP_TAB_RET.FIRST..V_EMP_TAB_RET.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||V_EMP_TAB_RET(I).EMPLOYEE_ID||', NAME -> '||V_EMP_TAB_RET(I).FIRST_NAME||' '||V_EMP_TAB_RET(I).LAST_NAME||', JOBID -> '||V_EMP_TAB_RET(I).JOB_ID||', SALARY -> '||V_EMP_TAB_RET(I).SALARY);
  END LOOP;
END;
/

LIMIT CLAUSE:

If you are fetching large no of rows to a collection then it can consume too much session memory and causes error. To avoid such error oracle provide the LIMIT clause. It restricts the no of rows to be accessed in a single fetch of a bulk operation. This way it reduces the consumption of session memory and performance will be increased.

EXAMPLE 1:
CREATE TABLE EMP_LIMIT_TEST
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       SALARY
FROM EMPLOYEES
WHERE 1 = 2;
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);
    FOR I IN V_EMP_TAB.FIRST..V_EMP_TAB.LAST
    LOOP
      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);
    END LOOP;
    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;
/

Author

Leave a Comment

Your email address will not be published.