MacLochlainns Weblog

PL/SQL is a great programming language as far as it goes but it lacks true type inheritance for its collections. While you can create an object type and subtype, you can’t work with collections of those types the same way. PL/SQL object type inheritance, unlike the Java class hierarchy and parallel array class hierarchy, only supports a class hierarchy. Effectively, that means:

• You can pass a subtype as a call parameter, or argument, to a parent data type in a function, procedure, or method signature, but
• You can’t pass a collection of a subtype as a call parameter, or argument, to a collection of parent type in a function, procedure, or method signature.

The limitation occurs because collections have their own data type, which is fixed when you create them. Worse yet, because Oracle has never seen fit to fix their two underlying code trees (23 years and counting since Oracle 8i), you have two types of collections using two distinct C/C++ libraries. You define collections of Attribute Data Types (ATDs) when you create a collection of a standard scalar data type, like NUMBER, VARCHAR2, or DATE. You define collection of User-Defined Data Types (UTDs) when you create a collection of a SQL UDT or PL/SQL-only RECORD data type. The former uses one C/C++ library and the latter another.

Now, Oracle even make the differences between Java and PL/SQL more complex because it treats collections known as tables, really lists in most programming languages, differently than varrays, or arrays. You create a TABLE collection, or list, when you create a table of a scalar or UDT data type. There are two options when you create these object types, and they are:

• You create an empty collection with a no element constructor, which means you’ll need to allocate memory before assigning element values later in your program.
• You create a populated collection with a comma-delimited list of elements.

Both approaches give you a list of elements with a densely populated index. A “densely populated index” is Oracle’s jargon for how they characterize a 1-based sequence of integers without any gaps (e.g., 1, 2, 3, …). The initial construction works the same way whether you create a TABLE or VARRAY collection type. Unfortunately, after you’ve built the collection behaviors change. If you use Oracle’s Collection API to delete one or more items from a TABLE collection type, you create gaps in the index’s sequence of values. That means you must use special logic to navigate across a TABLE collection type to ensure it doesn’t fail when encountering a gap in the numeric sequence.

For example, here’s a FOR-LOOP without the logic to vouchsafe a uninterrupted set of sequence values incrementing by a counter of 1 element at a time:

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

DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Statically allocate memory and assign values
     to for elements. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
BEGIN
  /* Remove the second element, Shemp, from the 
     collection of variable length strings. */
  lv_list.DELETE(2);
 
  /* Loop through the target with a for loop, which
     depends on densely populated index values. */
  FOR i IN 1..lv_list.COUNT LOOP
    dbms_output.put_line('['||lv_list(i)||']');
  END LOOP;
END;
/

The program fails when it tries to read the second element of the table collection, which was previously removed. It raises the following error message after print the first element of the table collection:

[Moe]
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 16

Conveniently, Oracle’s Collection API provides an EXISTS method that we can use to check for the presence of an index’s value. Modifying line 16 by wrapping it in an IF-statement fixes one problem but identifies another:

15
16
17
18
19

  FOR i IN 1..lv_list.COUNT LOOP
    IF lv_list.EXISTS(i) THEN
      dbms_output.put_line('['||lv_list(i)||']');
    END IF;
  END LOOP;

The program no longer fails on a missing index value, or index gap, but it returns fewer lines of output than you might expect.

That’s because the Oracle Collection API’s COUNT method returns the number of elements currently allocated in memory not the number of original elements. We learn that when we deleted the second element, Oracle deleted the memory allocated for it as well. This is the type of behavior you might expect for a singly linked list. It prints:

[Moe]
[Larry]

One more change is required to count past and to the highest index value. One line 15, change the COUNT method call to the LAST method call, which returns the highest index value.

15
16
17
18
19

  FOR i IN 1..lv_list.LAST LOOP
    IF lv_list.EXISTS(i) THEN
      dbms_output.put_line('['||lv_list(i)||']');
    END IF;
  END LOOP;

It now prints the three stooges we would expect to see:

[Moe]
[Larry]
[Curly]

Realistically, a FOR-LOOP is not the best control structure for a collection. You should use a WHILE-LOOP and treat the incrementing value as an iterator rather than sequence index value. An iterator doesn’t worry about gaps in the sequence, it simply moves to the next element in the singly linked list. Here’s an example that uses the iterator approach with a WHILE-LOOP:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

DECLARE
  /* Create a local table collection. */
  TYPE list IS TABLE OF VARCHAR2(10);
 
  /* Statically allocate memory and assign values
     to for elements. */
  lv_list  LIST := list('Moe','Shemp','Larry','Curly');
 
  /* Declare a current index variable. */
  CURRENT  NUMBER;
BEGIN
  /* Remove the second element, Shemp, from the 
     collection of variable length strings. */
  lv_list.DELETE(2);
 
  /* Loop through the target with a while loop, which
     doesn't depend on densely populated index values
     by setting the starting index value and increment
     as if with an iterator. */
  CURRENT := lv_list.FIRST;
  WHILE NOT (CURRENT > lv_list.LAST) LOOP
    dbms_output.put_line('['||lv_list(CURRENT)||']');
    CURRENT := lv_list.NEXT(CURRENT);
  END LOOP;
END;
/

The iterator approach prints the elements as:

[Moe]
[Larry]
[Curly]

You can reverse the process with the following changes to lines 20-24:

20
21
22
23
24

  CURRENT := lv_list.LAST;
  WHILE NOT (CURRENT < lv_list.FIRST) LOOP
    dbms_output.put_line('['||lv_list(CURRENT)||']');
    CURRENT := lv_list.PRIOR(CURRENT);
  END LOOP;

It prints the list backwards:

[Curly]
[Larry]
[Moe]

After covering the issues with sparsely populated, those with gaps in the sequence of indexes values, table collections, let’s examine how you must work around PL/SQL’s lack of a parallel array class hierarchy. The solution lies in combining two programming concepts:

• A function to pack the sparsely populated table collection into a densely populated one, and
• A package with overloaded functions that pack different table collections.

To develop the test case, let’s use an ADT collection because it’s the simplest to work with. The following creates a table collection of a thirty character long scalar string:

1
2
3

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

The following pack function takes a table collection of the thirty character long scalar string, evaluates the string for missing elements, and packs the existing elements into a densely populated list:

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

CREATE OR REPLACE
  FUNCTION pack
  ( pv_list  LIST ) RETURN list IS
 
  /* Declare a new list. */
  lv_new  LIST  := list();
BEGIN
  /* Read, check, and pack an old list into a new one. */
  FOR i IN 1..pv_list.LAST LOOP
    IF pv_list.EXISTS(i) THEN
      lv_new.EXTEND;
      lv_new(lv_new.COUNT) := pv_list(i);
    END IF;
  END LOOP;
  RETURN lv_new;
END;
/

This anonymous block tests the pack function:

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

DECLARE
  /* Declare a list value. */
  lv_test  LIST := list('Moe','Shemp','Larry','Curly');
BEGIN
  /* Remove one element in the middle. */
  lv_test.DELETE(2);
 
  /* Pack the list of elements into a sequence of values. */
  lv_test := pack(lv_test);
 
  /* Print the list of elements from the packed list. */
  FOR i IN 1..lv_test.COUNT LOOP
    dbms_output.put_line('['||lv_test(i)||']');
  END LOOP;
END;
/

It prints the expected three string values:

[Moe]
[Larry]
[Curly]

Now, let’s expand the example to build an overloaded package. The first step requires building a base_t object type and a table collection of the object type, like:

1
2
3
4
5
6
7
8
9

CREATE OR REPLACE
  TYPE base_t IS OBJECT
  ( oid  NUMBER )
  INSTANTIABLE NOT FINAL;
/
 
CREATE OR REPLACE
  TYPE base_list IS TABLE OF base_t;
/

Next, you create a book_t subtype of the base_t object type and a book_list table collection of the book_t subtype, like:

1
2
3
4
5
6
7
8
9

CREATE OR REPLACE
  TYPE book_t UNDER base_t
  ( title  VARCHAR2(30)
  , COST   NUMBER);
/
 
CREATE OR REPLACE
  TYPE book_list IS TABLE OF book_t;
/

We can test the base_t and book_t default constructors with the following SQL*Plus formatting and SQL query:

COL oid   FORMAT 999
COL title FORMAT A20
COL COST  FORMAT 99.99
SELECT *
FROM   TABLE(book_list(book_t(1,'Neuromancer',15.30)
                      ,book_t(2,'Count Zero',7.99)
                      ,book_t(3,'Mona Lisa Overdrive',7.99)
                      ,book_t(4,'Burning Chrome',8.89)));

It prints the following output:

 OID TITLE                  COST
---- -------------------- ------
   1 Neuromancer           15.30
   2 Count Zero             7.99
   3 Mona Lisa Overdrive    7.99
   4 Burning Chrome         8.89

The following is an overloaded package specification:

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

CREATE OR REPLACE
  PACKAGE packer IS
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  LIST ) RETURN list;
 
  /* A UDT list of base objects. */
  FUNCTION pack
  ( pv_list  BASE_LIST ) RETURN base_list;
 
  /* A UDT list of subtype objects. */
  FUNCTION pack
  ( pv_list  BOOK_LIST ) RETURN book_list;
 
END;
/

After you create the package specification, you need to provide the implementation. This is typical in any programming language that supports Interface Description Language (IDL). A package body provides the implementation for the package specification. The package body follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

CREATE OR REPLACE
  PACKAGE BODY packer IS
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  LIST ) RETURN list IS
 
    /* Declare a new list. */
    lv_new  LIST  := list();
  BEGIN
    /* Read, check, and pack an old list into a new one. */
    FOR i IN 1..pv_list.LAST LOOP
      IF pv_list.EXISTS(i) THEN
        lv_new.EXTEND;
        lv_new(lv_new.COUNT) := pv_list(i);
      END IF;
    END LOOP;
    RETURN lv_new;
  END pack;
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  BASE_LIST ) RETURN base_list IS
 
    /* Declare a new list. */
    lv_new  BASE_LIST  := base_list();
  BEGIN
    /* Read, check, and pack an old list into a new one. */
    FOR i IN 1..pv_list.LAST LOOP
      IF pv_list.EXISTS(i) THEN
        lv_new.EXTEND;
        lv_new(lv_new.COUNT) := pv_list(i);
      END IF;
    END LOOP;
    RETURN lv_new;
  END pack;
 
  /* A simple ADT list of strings. */
  FUNCTION pack
  ( pv_list  BOOK_LIST ) RETURN book_list IS
 
    /* Declare a new list. */
    lv_new  BOOK_LIST  := book_list();
  BEGIN
    /* Read, check, and pack an old list into a new one. */
    FOR i IN 1..pv_list.LAST LOOP
      IF pv_list.EXISTS(i) THEN
        lv_new.EXTEND;
        lv_new(lv_new.COUNT) := pv_list(i);
      END IF;
    END LOOP;
    RETURN lv_new;
  END pack;
 
END packer;
/

The test case for the base_list object type is:

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

DECLARE
  lv_test  BASE_LIST :=
    base_list(base_t(1),base_t(2)
             ,base_t(3),base_t(4));
BEGIN
  /* Remove one element in the middle. */
  lv_test.DELETE(2);
 
  /* Pack the list of elements into a sequence of values. */
  lv_test := packer.pack(lv_test);
 
  /* Print the list of elements from the packed list. */
  FOR i IN 1..lv_test.LAST LOOP
    dbms_output.put_line('['||lv_test(i).oid||']');
  END LOOP;
END;
/

It prints the following output:

[1]
[3]
[4]

The test case for the book_list object type is:

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

DECLARE
  lv_test  BOOK_LIST := 
    book_list(book_t(1,'Neuromancer',15.30)
             ,book_t(2,'Count Zero',7.99)
             ,book_t(3,'Mona Lisa Overdrive',7.99)
             ,book_t(4,'Burning Chrome',8.89));
BEGIN
  /* Remove one element in the middle. */
  lv_test.DELETE(2);
 
  /* Pack the list of elements into a sequence of values. */
  lv_test := packer.pack(lv_test);
 
  /* Print the list of elements from the packed list. */
  FOR i IN 1..lv_test.LAST LOOP
    dbms_output.put_line( '['||lv_test(i).oid||']'
                        ||'['||lv_test(i).title||']'
                        ||'['||lv_test(i).COST||']');
  END LOOP;
END;
/

It prints the following output:

[1][Neuromancer][15.3]
[3][Mona Lisa Overdrive][7.99]
[4][Burning Chrome][8.89]

In conclusion, you would not have to write overloaded methods for every list if PL/SQL supported class hierarchy and parallel array class hierarchy like Java. Unfortunately, it doesn’t and likely won’t in the future. You can pack table collections as a safety measure when they’re passed as parameters to other functions, procedures, or methods with the code above.

As always, I hope this helps those looking for a solution.