12.6 How do I…Use comma separated lists with DBMS_UTILITY?

Problem

I need a method to convert a comma separated list into a format I can easily use in PL/SQL. Some of our applications receive data in a comma separated format, and others are required to produce output in this format. Parsing character strings in PL/SQL can require a lot of code. How do I use PL/SQL built-in packages to use comma separated lists?

Technique

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:

DBMS_UTILITY.COMMA_TO_TABLE (list IN varchar2,

tablen OUT binary_integer,

tab OUT uncl_array);

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 (tab IN uncl_array,

tablen OUT binary_integer,

list OUT varchar2);

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 Table 12.10.

Table 12.10 DBMS_UTILITY miscellaneous functions/procedures

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. Run SQL*Plus and connect as the WAITE user account. CHP12_20, shown in Figure 12.23, creates the sample table and data used in the example. The sample table contains three columns and the sample data is converted to a comma separated list using PL/SQL tables. Run the script to create the sample table and its data.

SQL> START CHP12_20

Table created.

1 row created.

1 row created.

1 row created.

Commit complete.

2. Run the CHP12_21.SQL file in SQL*Plus, as shown in Figure 12.24. The PL/SQL code contained in the file converts a comma-separated list into a PL/SQL table and displays its contents.

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 CHP12_22.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. Figure 12.25 shows the results of the PL/SQL module in SQL*Plus.

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.