BULK COLLECT and Collections of RECORDS

How BULK COLLECT is used with Collections of Records?

We can declare a single Associative Array (Index-By tables) or Collection Type based on one of the following.

•         Table%ROWTYPE;

•         View%ROWTYPE;

•         User-Defined Record Type

•         Cursor%ROWTYPE

DECLARE

          /* Declare an associative array type of ALL_TABLES structure */

       TYPE V_Collection_Records IS TABLE OF All_Tables%ROWTYPE

       INDEX BY PLS_INTEGER;

       V_Rec V_Collection_Records;

BEGIN

      /* Fetch dat from USER_TABLES data in one pass*/

      SELECT *

      BULK COLLECT INTO V_rec

      FROM All_Tables;

     DBMS_OUTPUT.PUT_LINE ('Total Records Fetched From ALL_Tables : ' || TO_CHAR ( V_rec.COUNT ));

END;

SQL> DECLARE
  2            /* Declare an associative array type of ALL_TABLES structure */
  3         TYPE V_Collection_Records IS TABLE OF All_Tables%ROWTYPE
  4         INDEX BY PLS_INTEGER;
  5         V_Rec V_Collection_Records;
  6  BEGIN
  7        /* Fetch dat from USER_TABLES data in one pass*/
  8        SELECT *
  9        BULK COLLECT INTO V_rec
 10        FROM All_Tables;
 11       DBMS_OUTPUT.PUT_LINE ('Total Records Fetched From ALL_Tables : ' || TO_CHAR ( V_rec.COUNT ));
 12  END;
 13  /
Total Records Fetched From ALL_Tables : 150

PL/SQL procedure successfully completed.

So, we only need to declare one associative array variable, irrespective of the number of columns to be fetched. In the above example an Associative array has been defined as being of exactly the same structure of Source table. BULK Collect reduces the amount of code required in fetching, which results in performance gains as well as improved legibility and simpler maintenance.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.