BULK COLLECT Never Appends to Your Collection

View All Scripts Login to Run Script

Script Name BULK COLLECT Never Appends to Your Collection
Description BULK COLLECT always replaces the current contents of a collection before the fetch is executed. It never appends. I demonstrate this aspect of bulk fetching with a set of blocks. Ask yourself: which of the anonymous blocks in the steps below will display "Count=8" after execution? The code below is taken from a PL/SQL Challenge quiz: https://plsqlchallenge.oracle.com/pls/apex/f?p=10000:659:::NO:659:P659_COMP_EVENT_ID,P659_QUESTION_ID,P659_QUIZ_ID:1263706,19866,&cs=11B1EF99C5ADFDA921B9D23DA640AE407
Area PL/SQL General
Contributor Steven Feuerstein
Created Tuesday November 29, 2016

Statement 1

CREATE TABLE plch_parts
(
   partnum    INTEGER PRIMARY KEY,
   partname   VARCHAR2 (100) UNIQUE
)

Table created.

Statement 2

BEGIN
   INSERT INTO plch_parts
        VALUES (1, 'Mouse');

   INSERT INTO plch_parts
        VALUES (100, 'Keyboard');

   INSERT INTO plch_parts
        VALUES (500, 'Monitor');

   INSERT INTO plch_parts
        VALUES (501, 'No-res Webcam');

   INSERT INTO plch_parts
        VALUES (502, 'Ultraviolet Switch');

   INSERT INTO plch_parts
        VALUES (1000, 'Super Cable');

   INSERT INTO plch_parts
        VALUES (1001, 'USB17 Port');

   INSERT INTO plch_parts
        VALUES (1002, 'Bluetoothy Thing');

   COMMIT;
END;

1 row(s) inserted.

Statement 3

This way, it can be used across my entire code base - I mean my subsequent script steps.

Define Collection Type in Package Specification

CREATE OR REPLACE PACKAGE plch_pkg AUTHID DEFINER 
IS 
   TYPE array_t IS TABLE OF plch_parts.partname%TYPE 
      INDEX BY PLS_INTEGER; 
END;

Package created.

Statement 4

How many elements left in the collection at the end?

DECLARE 
   l_partnames   plch_pkg.array_t; 
 
   CURSOR parts_cur 
   IS 
        SELECT partname 
          FROM plch_parts 
      ORDER BY partnum; 
BEGIN 
   OPEN parts_cur; 
 
   LOOP 
      FETCH parts_cur BULK COLLECT INTO l_partnames LIMIT 2; 
 
      EXIT WHEN parts_cur%NOTFOUND; 
   END LOOP; 
 
   DBMS_OUTPUT.put_line ('Count='||l_partnames.COUNT); 
 
   CLOSE parts_cur; 
END;

Count=0

Statement 5
In this choice, I build my own append logic. The results of the latest BULK COLLECT fetch are loaded into l_partnames1 and then I copy them over to l_partnames2, appending them to whatever is already in the array. So you could say: Good, now I know how to get around the problem of BULK COLLECT never appending. But why would you do this? The whole point of FETCH-BULK COLLECT with a LIMIT is that you want to avoid allocating too much PGA memory by filling up the collection with all the rows that might be fetched. But when I copy/append into l_partnames2, I end up using the same amount of PGA (or a bit more, since I am also allocating PGA for l_partnames1).
Copying Most Recent Fetched Rows to Cumulative Array
```
DECLARE 
   l_partnames1   plch_pkg.array_t; 
   l_partnames2   plch_pkg.array_t; 
 
   CURSOR parts_cur 
   IS 
        SELECT partname 
          FROM plch_parts 
      ORDER BY partnum; 
BEGIN 
   OPEN parts_cur; 
 
   LOOP 
      FETCH parts_cur BULK COLLECT INTO l_partnames1 LIMIT 2; 
 
      FOR indx IN 1 .. l_partnames1.COUNT 
      LOOP 
         l_partnames2 (l_partnames2.COUNT + 1) := l_partnames1 (indx); 
      END LOOP; 
 
      EXIT WHEN parts_cur%NOTFOUND; 
   END LOOP; 
 
   DBMS_OUTPUT.put_line ('Count='||l_partnames2.COUNT); 
 
   CLOSE parts_cur; 
END;
```
```
Count=8
```
Statement 6
This choice displays the desired output - but the code doesn't make any sense. When I perform a FETCH-BULK COLLECT inside a loop like this, that is generally because I want to limit how many rows I retrieve with each fetch (thereby managing the allocation of PGA memory). But this FETCH does not have a LIMIT clause. So really there is no benefit to using an explicit cursor and loop here. If you really do want to get the whole set of rows in a single fetch, just use a SELECT-INTO with BULK COLLECT as shown in choice 4.
An Unlimited Fetch Inside a Loop? Huh?
```
DECLARE 
   l_partnames   plch_pkg.array_t; 
   l_count INTEGER; 
 
   CURSOR parts_cur 
   IS 
        SELECT partname 
          FROM plch_parts 
      ORDER BY partnum; 
BEGIN 
   SELECT COUNT (*) INTO l_count FROM plch_parts; 
 
   OPEN parts_cur; 
 
   LOOP 
      FETCH parts_cur BULK COLLECT INTO l_partnames; 
 
      EXIT WHEN l_partnames.COUNT = l_count; 
   END LOOP; 
 
   DBMS_OUTPUT.put_line ('Count='||l_partnames.COUNT); 
 
   CLOSE parts_cur; 
END;
```
```
Count=8
```
Statement 7
Short, sweet and simple. And maybe OK. So now I just fetch all rows with single round-trip to SQL engine, and fill up l_partnames with all the part names. If this is what you really need to do (get all rows and fill up a single collection) and you are sure you will always have enough PGA memory to do so, this approach is fine. If the amount of data being fetched is expected to grow - and perhaps grow quickly - over time, you should always use FETCH with LIMIT. No, you will not be able to append, but that's fine - that's the whole point: you don't want to append, or cannot afford to, in any case.
Short and Sweet
```
DECLARE 
   l_partnames   plch_pkg.array_t; 
BEGIN 
     SELECT partname 
       BULK COLLECT INTO l_partnames 
       FROM plch_parts 
   ORDER BY partnum; 
 
   DBMS_OUTPUT.put_line ('Count='||l_partnames.COUNT); 
END;
```
```
Count=8
```
Statement 8
```
DROP TABLE plch_parts
```
```
Table dropped.
```
Statement 9
```
DROP PACKAGE plch_pkg
```
```
Package dropped.
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database