How to loop through a delimited list in Oracle PLSQL

Question

I am working on an Oracle procedure that calls another procedure within it. One of my parameters (parm1) can contain one or more values in a comma separated list. How can I loop through these values to pass them one at a time to another procedure?

Here is an example of what I would like it to do:

When Parm1 = 123,312

callProcedure2(123)
callProcedure2(321)

-or-

When Parm1 123

callProcedure2(123)

I think this can be accomplished using a loop but I can't figure out how to get it to use each value as a separated call within the loop.

Any help would be appreciated!

Thanks!

Try looking for [`[Oracle]` and split](http://stackoverflow.com/search?q=%5Boracle%5D+split) This [question](http://stackoverflow.com/q/19109413/119477) looks helpful — Conrad Frix, Oct 03 '13 at 20:51

score 16 · Answer 1 · answered Oct 03 '13 at 20:52

16

CURSOR V_CUR IS
select regexp_substr(Parm1 ,'[^,]+', 1, level) As str from dual
connect by regexp_substr(Parm1, '[^,]+', 1, level) is not null;

This curor will give you result like this

123
321

Now iterate the cursor and call the procedure in loop.

For i IN V_CUR
LOOP
    callProdcedure2(i.str);
END LOOP;

answered Oct 03 '13 at 20:52

Vijay

1,024
6
18

This is exactly what I was looking for. – J. Chomel Mar 31 '16 at 06:58

score 9 · Accepted Answer · answered Oct 04 '13 at 14:46

Just loop through substrings:

declare 
  parm1 varchar2(1000) := '123,234,345,456,567,789,890';

  vStartIdx binary_integer;
  vEndIdx   binary_integer;
  vCurValue varchar2(1000);
begin

  vStartIdx := 0;
  vEndIdx   := instr(parm1, ','); 

  while(vEndIdx > 0) loop
    vCurValue := substr(parm1, vStartIdx+1, vEndIdx - vStartIdx - 1);

    -- call proc here
    dbms_output.put_line('->'||vCurValue||'<-');

    vStartIdx := vEndIdx;
    vEndIdx := instr(parm1, ',', vStartIdx + 1);  
  end loop;

  -- Call proc here for last part (or in case of single element)
  vCurValue := substr(parm1, vStartIdx+1);
  dbms_output.put_line('->'||vCurValue||'<-');

end;

Dudi Boy · Answer 3 · 2020-01-22T14:11:36.303

There is a utility procedure COMMA_TO_TABLE and array type DBMS_UTILITY.UNCL_ARRAY dedicated for this task. Since Oracle 10g.

It is well document here.

Here is a sample solution:

SET SERVEROUTPUT ON
DECLARE
    csvListElm VARCHAR2(4000) := 'elm1, elm2,elm3 ,elm4 , elm5';
    csvListTable DBMS_UTILITY.UNCL_ARRAY;
    csvListLen BINARY_INTEGER;
    currTableName VARCHAR2(222);
BEGIN
    DBMS_UTILITY.COMMA_TO_TABLE(csvListElm, csvListLen, csvListTable);
    FOR csvElm IN 1..(csvListTable.COUNT - 1) LOOP
        dbms_output.put_line('-- CSV element        : <'||csvListTable(csvElm)||'>');
        dbms_output.put_line('-- Trimmed CSV element: <'||trim(csvListTable(csvElm))||'>');
    END LOOP; 
END;
/

Sample output:

-- CSV element        : <elm1>;
-- Trimmed CSV element: <elm1>;
-- CSV element        : < elm2>;
-- Trimmed CSV element: <elm2>;
-- CSV element        : <elm3 >;
-- Trimmed CSV element: <elm3>;
-- CSV element        : <elm4 >;
-- Trimmed CSV element: <elm4>;
-- CSV element        : < elm5>;
-- Trimmed CSV element: <elm5>;

score 0 · Answer 4 · answered Mar 31 '16 at 08:27

It is possible to use a function that you can use in a for loop (without regexp for ThinkJet):

Create a type and function

CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE
       FUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)
         RETURN t_my_list
       AS
         l_string VARCHAR2(32767) := p_list || p_sep;
         l_sep_index PLS_INTEGER;
         l_index PLS_INTEGER := 1;
         l_tab t_my_list     := t_my_list();
       BEGIN
         LOOP
           l_sep_index := INSTR(l_string, p_sep, l_index);
           EXIT
         WHEN l_sep_index = 0;
           l_tab.EXTEND;
           l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));
           l_index            := l_sep_index + 1;
         END LOOP;
         RETURN l_tab;
       END cto_table;
/

Then how to call it in the for loop:

DECLARE
  parm1 varchar2(4000) := '123,234,345,456,567,789,890';
BEGIN
    FOR x IN (select * from (table(cto_table(',', parm1)) ) )
    LOOP
        dbms_output.put_line('callProdcedure2 called with ' || x.COLUMN_VALUE);
        callProdcedure2(x.COLUMN_VALUE);
    END LOOP;
END;
/

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

Result as expected:

callProdcedure2 called with 123
callProdcedure2 called with 234
...