14

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.

Community
  • 1
  • 1
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
  • 3
    Possible duplicate of [How to loop through a delimited list in Oracle](http://stackoverflow.com/questions/19168819/how-to-loop-through-a-delimited-list-in-oracle) – Sathyajith Bhat Mar 31 '16 at 06:40
  • 1
    But why PL/SQL when you could do it in plain SQL? See [**Split comma delimited string into rows**](https://lalitkumarb.wordpress.com/2014/12/02/split-comma-delimited-string-into-rows-in-oracle/) – Lalit Kumar B Mar 31 '16 at 06:47
  • Thanks, that would be my second choice to use `instr` inside a `while`. Is it possible to use a function which would return something usable inside a `for` loop. – J. Chomel Mar 31 '16 at 06:50
  • 1
    Thank you @Lalit, I think this will help me a lot. – J. Chomel Mar 31 '16 at 06:52

3 Answers3

32

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>
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • This worked for me. I will ask another more accurate question about the _function_ I am looking for. – J. Chomel Mar 31 '16 at 07:03
  • @J.Chomel You can create a user defined function. Or use Oracle's inbuilt package **DBMS_UTILITY**. Let me know if you post a different question, I can help you with an answer. – Lalit Kumar B Mar 31 '16 at 07:07
5

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
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
4
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;
Izik
  • 746
  • 1
  • 9
  • 25