Bulk Collect on collection Type 'object' in Oracle

user_12194071 0
Viewed 442

I have an collection Type object

create or replace 
TYPE     "COLLECTION_OBJECT"    AS OBJECT
(
    attribute1 integer,
    attribute2  date,
    attribute2 integer,
) ;

Then inside my pl/sql procedure I have collection table which is created like this.

create or replace 
TYPE         "COLLECTION_TABLE"       as table of COLLECTION_OBJECT;

I am doing Bulk collect like this.

SELECT COLLECTION_OBJECT(attribut1,attribut3,attribut3,attribute4) BULK COLLECT
  INTO result_set
  FROM TABLE(COLLECTION_TABLE)

I am collecting data in COLLECTION_OBJECT 3 times in pl/sql function and the above query adds data to result_set 3 time just after the data is collected in COLLECTION_OBJECT .

My Question is.

Will the data collected in COLLECTION_OBJECT will be cleared off each time it has been bulk collected into result_set or will it keep adding up till last?

I tried searching online got that when bulk collect runs it frees up the memory after it has read, but I didn't get anything related to bulk collect from Type object so I am not confident.

oracle19c oracle sql
1 Answers

Each time you use a bulk collect, it clears the collection and replaces it with the new data being collected. If you want to combine multiple bulk collects into a single collection, you will need to use a temporary collection, collect into that, then join the collections together using the MULTISET operator. An example of this can be found below.

Procedure

CREATE OR REPLACE TYPE COLLECTION_OBJECT AS OBJECT
(
    attribute1 INTEGER,
    attribute2 DATE,
    attribute3 INTEGER
);

CREATE OR REPLACE TYPE COLLECTION_TABLE AS TABLE OF COLLECTION_OBJECT;


DECLARE
    l_data        collection_table;
    l_temp_data   collection_table;
BEGIN
        SELECT COLLECTION_OBJECT (1, TO_DATE ('1-JAN-2020'), 2)
          BULK COLLECT INTO l_data
          FROM DUAL
    CONNECT BY LEVEL <= 5;

    DBMS_OUTPUT.put_line ('1. Count: ' || l_data.COUNT);

        SELECT COLLECTION_OBJECT (1, TO_DATE ('1-JAN-2020'), 2)
          BULK COLLECT INTO l_data
          FROM DUAL
    CONNECT BY LEVEL <= 7;

    DBMS_OUTPUT.put_line ('2. Count: ' || l_data.COUNT);

        SELECT COLLECTION_OBJECT (1, TO_DATE ('1-JAN-2020'), 2)
          BULK COLLECT INTO l_temp_data
          FROM DUAL
    CONNECT BY LEVEL <= 10;

    l_data := l_data MULTISET UNION ALL l_temp_data;

    DBMS_OUTPUT.put_line ('3. Count: ' || l_data.COUNT);
    
    SELECT COLLECTION_OBJECT (1, TO_DATE ('1-JAN-2020'), 2)
          BULK COLLECT INTO l_temp_data
          FROM DUAL
    CONNECT BY LEVEL <= 50;

    l_data := l_data MULTISET UNION ALL l_temp_data;

    DBMS_OUTPUT.put_line ('4. Count: ' || l_data.COUNT);
END;

Result

1. Count: 5
2. Count: 7
3. Count: 17
4. Count: 67
user_71100990
Related