The DBMS_UTILITY package contains two procedures for working with comma separated lists. The COMMA_TO_TABLE procedure converts a comma separated list into a PL/SQL table. Once the list is in a PL/SQL table, it becomes easy to work with. The specification for the procedure is shown here:

The list parameter contains the comma separated list moved to the table by the procedure. The TABLEN parameter is an OUT parameter, which returns the number of elements moved to the table. The TAB parameter returns the PL/SQL table containing the list to the calling module. The UNCL_ARRAY datatype is defined in the package and must be used in the procedure to declare the table variable. A variable can be defined as a datatype in a package by specifying the package name with the datatype. If you are working with a PL/SQL table and wish to move the values into a comma separated list, the TABLE_TO_COMMA procedure can be used. The specification for the procedure is shown here:

DBMS_UTILITY.TABLE_TO_COMMA (list IN varchar2,
                             tablen OUT binary_integer,
                             tab OUT uncl_array);

The TAB parameter specifies the table to be converted to a comma separated list. The TABLEN parameter is an OUT parameter returning the number of items moved to the list, and the LIST parameter returns the character string containing the comma separated list. Functions and procedures in the DBMS_UTILITY package provide a variety of useful utilities as listed in the following table:

Name	Type	Description
COMMA_TO_TABLE	Procedure	Converts a comma-separated list of names into a PL/SQL table of names.
TABLE_TO_COMMA	Procedure	Converts a PL/SQL table of names into a comma-separated list of names.
GET_TIME	Function	Finds the current time in the hundredths of a second.
GET_PARAMETER_VALUE	Function	Gets value of the specified parameter.
FORMAT_CALL_STACK	Function	Displays the current call stack.
FORMAT_ERROR_STACK	Function	Displays the current error stack.
COMPILE_SCHEMA	Procedure	Compiles all functions, procedures, and packages in the specified schema.
ANALYZE_SCHEMA	Procedure	Analyzes all tables, clusters, and indexes in a schema.
ANALYZE_DATABASE	Procedure	Analyzes all tables, clusters, and indexes in a database.
ANALYZE_PART_OBJECT	Procedure	Analyzes the schema object for each partition, in parallel.
EXEC_DDL_STATEMENT	Procedure	Executes the given DDL statement.
IS_PARALLEL_SERVER	Function	Returns TRUE if the database is running in parallel server mode, FALSE, or otherwise.
NAME_RESOLVE	Procedure	Gets RESOLVE the specified object name.
NAME_TOKENIZE	Procedure	Calls the parser to return a tokenized string.
MAKE_DATA_BLOCK_ADDRESS	Function	Creates a data block address from a file number and a block number.
DATA_BLOCK_ADDRESS_FILE	Function	Gets the file number part of a data block address.
DATA_BLOCK_ADDRESS_BLOCK	Function	Gets the block number part of a data block address.
GET_HASH_VALUE	Function	Computes the hash value of a given string.
PORT_STRING	Function	Returns a string identifying the version of Oracle and the operating system.
DB_VERSION	Procedure	Returns the database version.

Steps

1. Create the following table and insert some data:

create  table DEPT12
(
  dept_no       NUMBER(10)  ,
  dept_name     VARCHAR2(30),
  location      VARCHAR2(20) );
insert into dept12 values (1,'Marketing','Chicago');
insert into dept12 values (2,'Sales','Tampa');
insert into dept12 values (3,'I/S','New York');

2. Run the following sql file in SQL*Plus. The PL/SQL code contained in the file converts a comma-separated list into a PL/SQL table and displays its contents.

set echo on serveroutput on
declare
   --Declare the Table Variable (PL/SQL Table), will hold my data
   my_table     dbms_utility.uncl_array;
   -- Will count number of elements returned by the array
   cnt          binary_integer;
   comma_string varchar2(250);
begin
   comma_string := 'Illinois,Iowa,Indiana,Kentucky,Maryland,Montana';
   DBMS_UTILITY.COMMA_TO_TABLE(comma_string, cnt, my_table);
   for i in 1..cnt loop
      dbms_output.put_line(my_table(i));
   end loop;
end;
/

Line 1 declares a variable of the UNCL_ARRAY datatype created in the DBMS_UTILITY package. In order to pass the table as a parameter to the COMMA_TO_TABLE procedure, it must be declared as this datatype.
Line 2 declares a variable to be passed the number of elements processed in the list.
Line 3 declares the variable containing the comma separated list.
Line 6 moves a sample list into the COMMA_STRING variable. Line 7 calls the COMMA_TO_TABLE procedure.

The first parameter contains the list to be processed. The last two parameters are both OUT parameters used to return values from the procedure. The CNT variable receives the number of elements processed, and the MT_TABLE variable contains the PL/SQL table.

Lines 8 through 10 loop through each element in the table and display its values. Each element in the list is displayed as a separate line.

3. Run the following sql file in SQL*Plus. The PL/SQL code contained in the file reads records from the sample table, places the records in a PL/SQL table, and returns them as a comma-separated list

set echo on serveroutput on
declare
   cursor c1 is select dept_no, dept_name, location
                   from dept12;
                  
   output_table   dbms_utility.uncl_array;
   cnt            binary_integer;
   output_string  varchar2(80);
   counter integer:= 0;
begin
   for i in c1 loop
      output_table(counter+1) := I.dept_no;
      output_table(counter+2) := I.dept_name;
      output_table(counter+3) := I.location;
      counter := counter + 3;
   end loop;
   DBMS_UTILITY.TABLE_TO_COMMA(output_table, cnt, output_string);
   dbms_output.put_line(output_string);
end;
/

Lines 2 through 6 declare a cursor to query the sample table created in Step 1.
Line 7 declares a variable of the UNCL_ARRAY datatype, defined in the DBMS_UTILITY package.
Line 8 declares a variable used as an OUT parameter and passes the number of elements processed by the procedure.
Line 9 declares a variable used as an OUT parameter and returns the comma separated list from the procedure.
Line 10 declares a variable tracking the current element in the PL/SQL table.
The loop defined in lines 12 through 17 processes each record contained in the cursor.
Lines 13 through 15 move the values in the columns next to the records in the PL/SQL table.
Line 16 increments the counter.
Line 18 uses the TABLE_TO_COMMA function to move the records in the PL/SQL table into the character string as comma separated values.
Line 19 displays the comma separated list to the screen.

How It Works

The TABLE_TO_COMMA and COMMA_TO_TABLE procedures in the DBMS_UTILITY package can be used to work with comma separated lists in PL/SQL. The PL/SQL table used must be declared of the type UNCL_ARRAY, defined in the DBMS_UTILITY package. Step 1 creates a sample table with data used to demonstrate the procedures. Steps 2 and 3 present a PL/SQL module, which converts a comma separated list into a PL/SQL table using the COMMA_TO_TABLE procedure. Once the list is moved into the table, it can be manipulated easily within the PL/SQL code. Steps 4 and 5 present a PL/SQL module, which queries a database table and converts the results to a comma separated character string by using the TABLE_TO_COMMA procedure.

Comments

Working with comma separated lists can be difficult without the use of the DBMS_UTILITY package because you would have to use character functions and parse the comma separated list with PL/SQL code. The COMMA_TO_TABLE procedure is useful for creating a PL/SQL table from a comma separated list. Because a PL/SQL table works like a one-dimensional array, it is perfect for handling lists.