TABLE_TO_COMMA procedures - Convert a table of names into a comma-delimited list of names

The TABLE_TO_COMMA procedures convert an array of names into a comma-delimited list of names. Each array element becomes a list entry.

Note: The names must be formatted as valid identifiers.

Syntax

Parameters

tab: An input argument of type LNAME_ARRAY or UNCL_ARRAY that specifies the array containing names. See LNAME_ARRAY or UNCL_ARRAY for a description of tab.
tablen: An output argument of type INTEGER that returns the number of entries in list.
list: An output argument of type VARCHAR(32672) that returns the comma-delimited list of names from tab.

Authorization

EXECUTE privilege on the DBMS_UTILITY module.

Examples

Example 1: The following example first uses the COMMA_TO_TABLE_LNAME procedure to convert a comma-delimited list to a table. The TABLE_TO_COMMA_LNAME procedure then converts the table back to a comma-delimited list which is displayed.

SET SERVEROUTPUT ON@

CREATE OR REPLACE PROCEDURE table_to_comma(
  IN p_list VARCHAR(100))
BEGIN
  DECLARE r_lname DBMS_UTILITY.LNAME_ARRAY;
  DECLARE v_length INTEGER;
  DECLARE v_listlen INTEGER;
  DECLARE v_list VARCHAR(80);

  CALL DBMS_UTILITY.COMMA_TO_TABLE_LNAME(p_list, v_length, r_lname);
  CALL DBMS_OUTPUT.PUT_LINE('Table Entries');
  CALL DBMS_OUTPUT.PUT_LINE('-------------');
  BEGIN
    DECLARE i INTEGER DEFAULT 1;
    DECLARE LOOP_LIMIT INTEGER;
    SET LOOP_LIMIT = v_length;

    WHILE i <= LOOP_LIMIT DO
      CALL DBMS_OUTPUT.PUT_LINE(r_lname(i));
      SET i = i + 1;
    END WHILE;
  END;
  CALL DBMS_OUTPUT.PUT_LINE('-------------');
  CALL DBMS_UTILITY.TABLE_TO_COMMA_LNAME(r_lname, v_listlen, v_list);
  CALL DBMS_OUTPUT.PUT_LINE('Comma-Delimited List: ' || v_list);
END@

CALL table_to_comma('sample_schema.dept,sample_schema.emp,sample_schema.jobhist')@

This example results in the following output:

SET SERVEROUTPUT ON
DB20000I  The SET SERVEROUTPUT command completed successfully.

CREATE OR REPLACE PROCEDURE table_to_comma(
  IN p_list VARCHAR(100))
BEGIN
  DECLARE r_lname DBMS_UTILITY.LNAME_ARRAY;
  DECLARE v_length INTEGER;
  DECLARE v_listlen INTEGER;
  DECLARE v_list VARCHAR(80);

  CALL DBMS_UTILITY.COMMA_TO_TABLE_LNAME(p_list, v_length, r_lname);
  CALL DBMS_OUTPUT.PUT_LINE('Table Entries');
  CALL DBMS_OUTPUT.PUT_LINE('-------------');
  BEGIN
    DECLARE i INTEGER DEFAULT 1;
    DECLARE LOOP_LIMIT INTEGER;
    SET LOOP_LIMIT = v_length;

    WHILE i <= LOOP_LIMIT DO
      CALL DBMS_OUTPUT.PUT_LINE(r_lname(i));
      SET i = i + 1;
    END WHILE;
  END;
  CALL DBMS_OUTPUT.PUT_LINE('-------------');
  CALL DBMS_UTILITY.TABLE_TO_COMMA_LNAME(r_lname, v_listlen, v_list);
  CALL DBMS_OUTPUT.PUT_LINE('Comma-Delimited List: ' || v_list);
END
DB20000I  The SQL command completed successfully.

CALL table_to_comma('sample_schema.dept,sample_schema.emp,sample_schema.jobhist')

  Return Status = 0

Table Entries
-------------
sample_schema.dept
sample_schema.emp
sample_schema.jobhist
-------------
Comma-Delimited List: sample_schema.dept,sample_schema.emp,sample_schema.jobhist