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

Question

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 https://stackoverflow.com/a/19184203/6019417)?

Many thanks in advance.

Answer 1

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

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>

Answer 2

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;

In the first line you define a table of any type you want.

then create variable of that type and give it values with a kind of constructor.

I initialized it in the declaration but it can be done also in the body of the Pl Sql.

Then just loop over your array from first index to the last.