MacLochlainns Weblog

One of the problems with Oracle’s Collection is there implementation of lists, which they call object tables. For example, you declare a collection like this:

CREATE OR REPLACE
  TYPE list IS TABLE OF VARCHAR2(10);
/

A table collection like the LIST table above is always initialized as a densely populated list. However, over time the list’s index may become sparse when an item is deleted from the collection. As a result, you have no guarantee of a dense index when you pass a table collection to a function. That leaves you with one of two options, and they are:

• Manage all collections as if they’re compromised in your PL/SQL blocks that receive a table collection as a parameter.
• Defrag indexes before passing them to other blocks.

The first option works but it means a bit more care must be taken with how your organization develops PL/SQL programs. The second option defrays a collection. It requires that you write a DEFRAG() function for each of your table collections. You should probably put them all in a package to keep track of them.

While one may think the function is as easy as assigning the old table collection to a new table collection, like:

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE OR REPLACE
  FUNCTION defrag
  ( sparse  LIST ) RETURN LIST IS
  /* Declare return collection. */
  dense  LIST := list();
BEGIN
  /* Mimic an iterator in the loop. */
  dense := sparse;
 
  /* Return the densely populated collection. */
  RETURN dense;
END defrag;
/

Line 8 assign the sparse table collection to the dense table collection without any changes in the memory allocation or values of the table collection. Effectively, it does not defrag the contents of the table collection. The following DEFRAG() function does eliminate unused memory and reindexes the table collection:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE OR REPLACE
  FUNCTION defrag
  ( sparse  LIST ) RETURN LIST IS
  /* Declare return collection. */
  dense  LIST := list();
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Mimic an iterator in the loop. */
  CURRENT := sparse.FIRST;
  WHILE NOT (CURRENT > sparse.LAST) LOOP
    dense.EXTEND;
    dense(dense.COUNT) := sparse(CURRENT);
    CURRENT := sparse.NEXT(CURRENT);
  END LOOP;
  /* Return the densely populated collection. */
  RETURN dense;
END defrag;
/

You can test the DEFRAG() function with this anonymous PL/SQL block:

DECLARE  
  /* Declare the collection. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Create a gap in the densely populated index. */
  lv_list.DELETE(2);
 
  /* Mimic an iterator in the loop. */
  CURRENT := lv_list.FIRST;
  WHILE NOT (CURRENT > lv_list.LAST) LOOP
    dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']');
    CURRENT := lv_list.NEXT(CURRENT);
  END LOOP;
 
  /* Print a line break. */
  dbms_output.put_line('----------------------------------------');
 
  /* Call defrag function. */
  lv_list := defrag(lv_list);
 
  FOR i IN 1..lv_list.COUNT LOOP
    dbms_output.put_line('['||i||']['||lv_list(i)||']');
  END LOOP;
END;
/

which prints the before and after state of the defrayed table collection:

[1][Moe]
[3][Larry]
[4][Curly]
----------------------------------------
[1][Moe]
[2][Larry]
[3][Curly]

As always, I hope this helps those trying to sort out a feature of PL/SQL. In this case, it’s a poorly documented feature of the language.