comma_to_table UTILITY

at times we face some requirements where we have to pass delimited values into an array(collection in Oracle).comma_to_table is a built-in utility to achieve the result with some predefined exception. Basically this utility is not written for this purpose.rather to use them internally in oracle parse IDENTIFIERS.

  Param_list1   VARCHAR2(50) := ‘A,B,C,D,E’; — valid List
  Param_list2   VARCHAR2(50) := ‘A,1,2,D,E’; — invalid List
  table_count   BINARY_INTEGER;
  tabl          DBMS_UTILITY.uncl_array;
  DBMS_OUTPUT.put_line(‘Table List : ‘ || Param_list1);

  DBMS_UTILITY.comma_to_table (
     list   => Param_list1,
     tablen => table_count,
     tab    => tabl);

  FOR i IN 1 .. table_count
    DBMS_OUTPUT.put_line(i || ‘ : ‘ || tabl(i));

Caution :
(1)  Never use COUNT while looping using the collection because  it always     return an extra row having NULL value.
       FOR i IN 1 .. table_count — valid
       FOR i IN 1 .. table_count.count — invalid

(2) Trying to pass numbers, elements greater than 30 characters, reserved words etc. will not work as the utility is using NAME_TOKENIZE function.

1 thought on “comma_to_table UTILITY

Leave a Reply

Your email address will not be published. Required fields are marked *