Problem
You have a comma-delimited values, which you want to convert to a table of values.Solution
There are many way to do this, I thought this quite straight-forward.declare
v_tablen BINARY_INTEGER;
l_tab DBMS_UTILITY.uncl_array;
cursor cur is select '"Caroline Dixon","Barry Brierley"' val from dual;
rec cur%rowtype;
BEGIN
open cur;
loop
fetch cur into rec;
exit when cur%notfound;
DBMS_UTILITY.comma_to_table (
list => rec.val,
tablen => v_tablen,
tab => l_tab);
FOR i IN 1 .. v_tablen LOOP
DBMS_OUTPUT.put_line(i || ' : ' || l_tab(i));
END LOOP;
end loop;
close cur;
Limitations:
If you've got spaces in your data, it needs to be enclosed by ".
Example
an apple, an orange
...will return ORA-20001: comma-separated list invalid near...
It should be "an apple","an orange"
Comments
Post a Comment