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

The COMMA_TO_TABLE procedure converts a comma-delimited list of names into an array of names. Each entry in the list becomes an element in the array.

Note: The names must be formatted as valid identifiers.

Syntax

Parameters

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

Authorization

EXECUTE privilege on the DBMS_UTILITY module.

Examples

Example 1: The following procedure uses the COMMA_TO_TABLE_LNAME procedure to convert a list of names to a table. The table entries are then displayed.

SET SERVEROUTPUT ON@

CREATE OR REPLACE PROCEDURE comma_to_table(
  IN p_list VARCHAR(4096))
BEGIN
  DECLARE r_lname DBMS_UTILITY.LNAME_ARRAY;
  DECLARE v_length INTEGER;
  CALL DBMS_UTILITY.COMMA_TO_TABLE_LNAME(p_list, v_length, r_lname);
  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;
END@

CALL comma_to_table('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 comma_to_table(
  IN p_list VARCHAR(4096))
BEGIN
  DECLARE r_lname DBMS_UTILITY.LNAME_ARRAY;
  DECLARE v_length INTEGER;
  CALL DBMS_UTILITY.COMMA_TO_TABLE_LNAME(p_list, v_length, r_lname);
  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;
END
DB20000I  The SQL command completed successfully.

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

  Return Status = 0

sample_schema.dept
sample_schema.emp
sample_schema.jobhist