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 itself.like parse IDENTIFIERS.
DECLARE
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;
BEGIN
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
LOOP
DBMS_OUTPUT.put_line(i || ‘ : ‘ || tabl(i));
END LOOP;
END;
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.