Using DBMS_UTILITY to aggregate strings

In my presentation "Analytic Functions Revisited" there is a section on how to string together some columns, string aggregation. Why? There is a new function in Oracle 11g Release 2, called LISTAGG which allows you to do this. This LISTAGG function also has an Analytic counterpart and that's the reason it is in the presentation.
At the UKOUG TEBS conference I did this presentation and someone approached me after wards to talk about these techniques. It boiled down to "Why do it the hard way when there is a built in package that allows you to do this for you?"
The built in package he was referring to is, note the title of this blog entry, DBMS_UTILITY. There are some downsides of using DBMS_UTILITY, more than I initially thought.

There is a procedure which allows you to change an Associative Array into a comma delimited string. This procedure is called: "TABLE_TO_COMMA".
First the version on which I ran these scripts:

SQL> select *
  2    from v$version
  3  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Now for the proper usage of the TABLE_TO_COMMA procedure:

SQL> declare
  2     names_list dbms_utility.uncl_array;
  3     len  number;
  4     str varchar2(32767);
  5  begin
  6     names_list (1) := 'this';
  7     names_list (2) := 'that';
  8     names_list (3) := 'the other';
  9     dbms_utility.table_to_comma (tab => names_list
 10                                 ,tablen => len
 11                                 ,list => str
 12                                 );
 13     dbms_output.put_line ('The delimited string: '||str);
 14     dbms_output.put_line ('The length of the collection is '||to_char (len));
 15  end;
 16  /
The delimited string: this,that,the other
The length of the collection is 3

PL/SQL procedure successfully completed.

As you can see in the results the collection is converted to a comma separated string. The number of elements in the collection is also shown.

The first thing that popped into mind was the most notable limitation of DBMS_UTILITY.TABLE_TO_COMMA: you can't pass in a collection of numbers.

SQL> declare
  2     names_list dbms_utility.uncl_array;
  3     len  number;
  4     str varchar2(32767);
  5  begin
  6     names_list (1) := 1;
  7     names_list (2) := 2;
  8     names_list (3) := 3;
  9     dbms_utility.table_to_comma (tab => names_list
 10                                 ,tablen => len
 11                                 ,list => str
 12                                 );
 13     dbms_output.put_line ('The delimited string: '||str);
 14     dbms_output.put_line ('The length of the collection is '||to_char (len));
 15  end;
 16  /
The delimited string: 1,2,3
The length of the collection is 3

PL/SQL procedure successfully completed.

Euh,... well, you can now.
The signature of TABLE_TO_COMMA in Oracle 9i was:

DBMS_UTILITY.TABLE_TO_COMMA ( 
   tab    IN  UNCL_ARRAY, 
   tablen OUT BINARY_INTEGER,
   list   OUT VARCHAR2); 

The definition of UNCL_ARRAY (Oracle 9i) is:

Lists of "USER"."NAME"."COLUMN"@LINK should be stored here.

And because of this you couldn't pass in numbers. Numbers are not valid identifiers.
apparently things changed, passing in numbers is allowed nowadays.

Oracle 10g introduced this overloading:

DBMS_UTILITY.TABLE_TO_COMMA ( 
   tab    IN  lname_array,
   tablen OUT BINARY_INTEGER,
   list   OUT VARCHAR2);

Since that limitation is gone, should you use TABLE_TO_COMMA to aggregate strings? I don't think you should (but it depends - as always). If you need data from the database using SQL, you might as well stay within the SQL realm and use SQL techniques to accomplish this goal.

There is some other oddities I just discovered playing with TABLE_TO_COMMA. The procedure only works with dense collections starting from 1 (one). Using a sparse collection won't show you the complete collection:

SQL> declare
  2     names_list dbms_utility.uncl_array;
  3     len  number;
  4     str varchar2(32767);
  5  begin
  6     names_list (1) := 'this';
  7     names_list (2) := 'that';
  8     names_list (3) := 'the other';
  9     names_list (42) := 'something';
 10     dbms_utility.table_to_comma (tab => names_list
 11                                 ,tablen => len
 12                                 ,list => str
 13                                 );
 14     dbms_output.put_line ('The delimited string: '||str);
 15     dbms_output.put_line ('The length of the collection is '||to_char (len));
 16  end;
 17  /
The delimited string: this,that,the other
The length of the collection is 3

PL/SQL procedure successfully completed.

Starting at a different value won't show you anything

SQL> declare
  2     names_list dbms_utility.uncl_array;
  3     len  number;
  4     str varchar2(32767);
  5  begin
  6     names_list (11) := 'this';
  7     names_list (12) := 'that';
  8     names_list (13) := 'the other';
  9     names_list (14) := 'something';
 10     dbms_utility.table_to_comma (tab => names_list
 11                                 ,tablen => len
 12                                 ,list => str
 13                                 );
 14     dbms_output.put_line ('The delimited string: '||str);
 15     dbms_output.put_line ('The length of the collection is '||to_char (len));
 16  end;
 17  /
The delimited string:
The length of the collection is 0

PL/SQL procedure successfully completed.

All the entries starting at zero (or lower) won't show up in the output.

SQL> declare
  2     names_list dbms_utility.uncl_array;
  3     len  number;
  4     str varchar2(32767);
  5  begin
  6     names_list (0) := 'what';
  7     names_list (1) := 'this';
  8     names_list (2) := 'that';
  9     names_list (3) := 'the other';
 10     names_list (4) := 'something';
 11     dbms_utility.table_to_comma (tab => names_list
 12                                 ,tablen => len
 13                                 ,list => str
 14                                 );
 15     dbms_output.put_line ('The delimited string: '||str);
 16     dbms_output.put_line ('The length of the collection is '||to_char (len));
 17  end;
 18  /
The delimited string: this,that,the other,something
The length of the collection is 4

PL/SQL procedure successfully completed.

Because of these oddities I wouldn't use DBMS_UTILITY.TABLE_TO_COMMA to create a comma separated string.
Links
Oracle Documentation on LISTAGG
Oracle 9i Documentation on DBMS_UTILITY
Oracle 10g Documentation on DBMS_UTILITY
Oracle 11g Documentation on DBMS_UTILITY
Tim Hall on String Aggregation Techniques