Use Oracle PL/SQL For Loop to iterate through comma delimited string

I am writing a piece of code that would need to iterate on the content of a string, each values being separated with a `,`.

e.g. I have my `elements`

v_list_pak_like varchar2(4000) := 'PEBO,PTGC,PTTL,PTOP,PTA';

How can I get it into an Array / Cursor to iterate on it in my loop?
---

for x in (elements)
loop
-- do my stuff
end loop;

I am looking for the very simple way, if possible avoiding to declare associative arrays.

**Would it be possible to create a function** that would return something usable as an input for a `for` loop (opposite to the while that could be used like in

[To see links please register here]

)?

Many thanks in advance.

You could do it easily in pure **SQL**. there are multiple ways of doing it, see **[Split comma delimited string into rows in Oracle][1]**

However, if you really want to do it in **PL/SQL**, then you could do it as:

SQL> set serveroutput on
SQL> DECLARE
2 str VARCHAR2(100) := 'PEBO,PTGC,PTTL,PTOP,PTA';
3 BEGIN
4 FOR i IN
5 (SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) l
6 FROM dual
7 CONNECT BY LEVEL <= regexp_count(str, ',')+1
8 )
9 LOOP
10 dbms_output.put_line(i.l);
11 END LOOP;
12 END;
13 /
PEBO
PTGC
PTTL
PTOP
PTA

PL/SQL procedure successfully completed.

SQL>


[1]:

[To see links please register here]

Thanks to Lalit great instructions, I am able to create a function that I can call from my `for` loop:

Create a type and function
-----------
CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE
FUNCTION comma_to_table(p_list IN VARCHAR2)
RETURN t_my_list
AS
l_string VARCHAR2(32767) := p_list || ',';
l_comma_index PLS_INTEGER;
l_index PLS_INTEGER := 1;
l_tab t_my_list := t_my_list();
BEGIN
LOOP
l_comma_index := INSTR(l_string, ',', l_index);
EXIT
WHEN l_comma_index = 0;
l_tab.EXTEND;
l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_comma_index - l_index));
l_index := l_comma_index + 1;
END LOOP;
RETURN l_tab;
END comma_to_table;
/

Then how to call it in my _for_ loop:
-----------

declare
v_list_pak_like varchar2(4000) := 'PEBO,PTGC,PTTL,PTOP,PTA';
begin
FOR x IN (select * from (table(comma_to_table(v_list_pak_like)) ) )
loop
dbms_output.put_line(x.COLUMN_VALUE);
end loop;
end;
/

Notice the default name `COLUMN_VALUE` given by Oracle that is necessary for the use I want to make of the result.

Result as expected:
---------

PEBO
PTGC
PTTL
PTOP
PTA

declare
type array_type is table of VARCHAR2(255) NOT NULL;
my_array array_type := array_type('aaa','bbb','ccc');
begin
for i in my_array.first..my_array.last loop
dbms_output.put_line( my_array(i) );
end loop;
end;

The first line defines a table of any type you want.

then declare a variable of that type and give its values with a constructor.

Then loop over the result from the first index to the last.

Edit - A solution that handles true string:

declare
str VARCHAR2(1024) := 'aaa,bbb,ccc';
type ARRAY_TYPE is table of VARCHAR2(255) NOT NULL;
my_array ARRAY_TYPE;
begin
SELECT REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) BULK COLLECT INTO my_array
FROM DUAL
CONNECT BY REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) IS NOT NULL;

for i in my_array.first..my_array.last loop
dbms_output.put_line( my_array(I));
end loop;
end;