MacLochlainns Weblog

If you implement street address as a nested table (or collection), the problem is printing an address book using only a SQL statement. The problem comes from matching up the multiple line return from the TABLE function with the rest of the row. That’s not a problem when you denormalized the list into known columns.

A PL/SQL function can convert the list into a scalar value. It’s an easy way to leverage a nested table. You can find the full code here. You might think that this is a neat opportunity to use a result cache function. I did, but the behavior isn’t presently supported. On compilation of a function with the RESULT_CACHE clause and a scalar collection input parameter, you raise a PLS-00999 error.

The message tells you:

RESULT_CACHE IS disallowed ON subprograms WITH IN parameter OF (OR containing) nested TABLE TYPE

Have you ever wondered how to decrement index values in a PL/SQL range for-loop. You’ve probably heard that you can’t. That’s part true and part false.

The true part is two fold. First, range for-loops move forward from a starting low value to high value. Second, the loop exits immediately when your starting value is high and ending value low. If you use the REVERSE keyword, the loop moves backward from an ending point to the starting point. This happens because a PL/SQL range for-loop is a guard on entry loop. The starting value is compared against the ending value and exits when the starting value is greater than the ending value.

The false part happens when you want to traverse a collection backward. Oracle collections are varrays, tables, or associative arrays. Associative arrays are also known as PL/SQL tables. You overcome the ascending only limitation by using the REVERSE keyword or leveraging the Oracle Collection API library functions.

This code works when the index is numeric:

DECLARE
  TYPE collection IS TABLE OF VARCHAR2(20);
  fellowship COLLECTION := collection('Frodo','Gandalf','Aragorn');
BEGIN
  -- Increasing iterator values.
  FOR i IN REVERSE 1..fellowship.COUNT LOOP
    DBMS_OUTPUT.put('Iterator ['||i||'] ');
    DBMS_OUTPUT.put('Value ['||fellowship(i)||']');
    DBMS_OUTPUT.new_line();
  END LOOP;
END;
/

This code works when the index is a string in an associative array:

DECLARE
  CURRENT VARCHAR2(8);
  element VARCHAR2(20);
  TYPE collection IS TABLE OF VARCHAR2(20)
    INDEX BY VARCHAR2(8);
  fellowship COLLECTION;
BEGIN
  -- Assign character indexed values.
  fellowship('Hobbit') := 'Frodo';
  fellowship('Wizard') := 'Gandalf';
  fellowship('Human')  := 'Aragorn';
  -- Increasing iterator values.
  FOR i IN 1..fellowship.COUNT LOOP
    IF i = 1 THEN -- Set starting point and assign value.
      CURRENT := fellowship.LAST;
      element := fellowship(CURRENT);
    ELSE -- Change value until you read the first element.
      IF fellowship.PRIOR(CURRENT) IS NOT NULL THEN
        CURRENT := fellowship.PRIOR(CURRENT);
        element := fellowship(CURRENT);
      ELSE -- Exit when all elements read.
        EXIT;
      END IF;
    END IF;
    DBMS_OUTPUT.put('Iterator ['||i||'] ');
    DBMS_OUTPUT.put('Index ['||CURRENT||'] ');
    DBMS_OUTPUT.put('Value ['||element||']');
    DBMS_OUTPUT.new_line();
  END LOOP;
END;
/

Chris Neumüller suggested another example with a WHILE loop:

DECLARE
  TYPE collection IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(8);
  fellowship COLLECTION;
  CURRENT    VARCHAR2(20);
BEGIN
  -- Assign character indexed values.
  fellowship('Hobbit') := 'Frodo';
  fellowship('Wizard') := 'Gandalf';
  fellowship('Human')  := 'Aragorn';
  -- Increasing iterator values.
  IF fellowship.COUNT > 0 THEN
    CURRENT := fellowship.LAST;
    WHILE CURRENT IS NOT NULL LOOP
      DBMS_OUTPUT.put('Iterator ['||CURRENT||'] ');
      DBMS_OUTPUT.put('Value ['||fellowship(CURRENT)||'] ');
      DBMS_OUTPUT.new_line();
      CURRENT := fellowship.PRIOR(CURRENT);
    END LOOP;
  END IF;
END;
/

While the former works, you should consider changing the cursor that populates the collection by adding a descending ORDER BY clause when indexing with a string. The algorithm is a bit more work but it also supports sparsely populated indexes and string-indexed associative arrays. All you need do is swap the LAST for FIRST and PRIOR for NEXT to go forward through a sparsely populated index.