creating table of varchar dynamically

Question

I'm trying to create a table of varChar to format a string (basically I'm trying to do a cheater split() function).

so if I do

type tableOfStrings is table of varchar2(50);
vWords tableOfStrings := tableOfStrings ('a','few','words');
dbms_output.put_line(vwords.count); -- 3

but if I try

type tableOfStrings  is table of varchar2(50);
vTemp varchar(300) := '''a'',''few'',''words''';
vWords tableOfStrings := tableOfStrings (vTemp);
dbms_output.put_line(vwords.count); -- 1 (it's a single string 'a','few','words')

I'd like to pass a string to a function and have each word added to the table.

Something like

FUNCTION FLABELFORMAT(pMyString in varchar2) return varchar2

type tableOfStrings  is table of varchar2(50);
vWords tableOfStrings := tableOfStrings (pMyString);

for i in 1 .. vWords.count loop
    {do some cool stuff}
end loop;
END FLABELFORMAT 

How can I accomplish using something like vWords tableOfStrings := tableOfStrings (pMyString); and have it add each value to the table?

TIA

Answer 1

Based on this answer, try this:

SELECT regexp_substr('a,few,words', '[^,]+', 1, LEVEL)
FROM dual
CONNECT BY regexp_substr('a,few,words', '[^,]+', 1, LEVEL) IS NOT NULL;