MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle’s Sparse Lists

without comments

Oracle’s PL/SQL Programming Language is really quite nice. I’ve written 8 books on it and still have fun coding in it. One nasty little detail about Oracle’s lists, introduced in Oracle 8 as PL/SQL Tables according their documentation, is they rely on sequential numeric indexes. Unfortunately, Oracle lists support a DELETE method, which can create gaps in the sequential indexes.

Oracle calls a sequence without gaps densely populated and a sequence with gaps sparsely populated. This can cause problems when PL/SQL code inadvertently removes elements at the beginning, end, or somewhere in the middle of the list. That’s because a program can then pass the sparsely populated list as a parameter to another stored function or procedure where the developer may traverse the list in a for-loop. That traversal may raise an exception in a for-loop, like this when it has gaps in the index sequence:

DECLARE
*
ERROR AT line 1:
ORA-01403: no data found
ORA-06512: AT line 20

Oracle’s myriad built-in libraries don’t offer a function to compact a sparsely populated list into a densely populated list. This post provides a compact stored procedure that converts a sparsely populated list to a densely populated list.

The first step to using the compact stored procedure requires that you create an object type in SQL, like this list of 20-character strings:

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

Now, you can implement the compact stored procedure by passing the User-Defined Type as it’s sole parameter.

CREATE OR REPLACE
  PROCEDURE compact ( sparse IN OUT LIST ) IS
    /* Declare local variables. */
    iterator  NUMBER;           -- Leave iterator as null.
 
    /* Declare new list. */
    dense     LIST := list();
  BEGIN
    /*
      Initialize the iterator with the starting value, which is
      necessary because the first element of the original list
      could have been deleted in earlier operations. Setting the
      initial iterator value to the first numeric index value
      ensures you start at the lowest available index value.
    */
    iterator := sparse.FIRST;
 
    /* Convert sparsely populated list to densely populated. */
    WHILE (iterator <= sparse.LAST) LOOP
      dense.EXTEND;
      dense(dense.COUNT) := sparse(iterator);
      iterator := sparse.NEXT(iterator);
    END LOOP;
 
    /* Replace the input parameter with the compacted list. */
    sparse := dense;
  END;
/

Before we test the compact stored procedure, let’s create deleteElement stored procedure for our testing:

CREATE OR REPLACE
  PROCEDURE deleteElement ( sparse   IN OUT LIST
                          , element  IN     NUMBER ) IS
  BEGIN
    /* Delete a value. */
    sparse.DELETE(element);
  END;
/

Now, let’s use an anonymous block to test compacting a sparsely populated list into a densely populated list. The test program will remove the first, last, and one element in the middle before printing the sparsely populated list’s index and string values. This test will show you gaps in the remaining non-sequential index values.

After you see the gaps, the test program compacts the remaining list values into a new densely populated list. It then prints the new index values with the data values.

DECLARE
  /* Declare a four item list. */
  lv_strings  LIST := list('one','two','three','four','five','six','seven');
BEGIN
  /* Check size of list. */
  dbms_output.put_line('Print initial list size:  ['||lv_strings.COUNT||']');
  dbms_output.put_line('===================================');
 
  /* Delete a value. */
  deleteElement(lv_strings,lv_strings.FIRST);
  deleteElement(lv_strings,3);
  deleteElement(lv_strings,lv_strings.LAST);
 
  /* Check size of list. */
  dbms_output.put_line('Print modified list size: ['||lv_strings.COUNT||']');
  dbms_output.put_line('Print max index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']');
  dbms_output.put_line('===================================');
  FOR i IN 1..lv_strings.LAST LOOP
    IF lv_strings.EXISTS(i) THEN
      dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']');
    END IF;
  END LOOP;
 
  /* Call a procedure by passing current sparse collection and
     the procedure returns dense collection. */
  dbms_output.put_line('===================================');
  dbms_output.put_line('Compacting list.');
  compact(lv_strings);
  dbms_output.put_line('===================================');
 
  /* Print the new maximum index value and list size. */
  dbms_output.put_line('Print new index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']');
  dbms_output.put_line('===================================');
  FOR i IN 1..lv_strings.COUNT LOOP
    dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']');
  END LOOP;
  dbms_output.put_line('===================================');
END;
/

It produces output, like:

Print initial list size:  [7]
===================================
Print modified list size: [4]
Print max index and size: [6][4]
===================================
List list index and item: [2][two]
List list index and item: [4][four]
List list index and item: [5][five]
List list index and item: [6][six]
===================================
Compacting list.
===================================
Print new index and size: [4][4]
===================================
List list index and item: [1][two]
List list index and item: [2][four]
List list index and item: [3][five]
List list index and item: [4][six]
===================================

You can extend this concept by creating User-Defined Types with multiple attributes, which are essentially lists of tuples (to draw on Pythonic lingo).

Written by maclochlainn

October 4th, 2021 at 11:49 pm