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>