Split string and iterate for each value in a stored procedure

You can use DBMS_UTILITY.COMMA_TO_TABLE.

The DBMS_UTILITY package provides various utility subprograms. One such useful utility is COMMA_TO_TABLE procedure, which converts a comma-delimited list of names into a PL/SQL table of names.

For example,

SQL> set serveroutput on;SQL> DECLARE  2    l_tablen BINARY_INTEGER;  3    l_tab DBMS_UTILITY.uncl_array;  4    CURSOR cur  5    IS  6      SELECT 'word1, word2, word3, word4, word5, word6' val FROM dual;  7    rec cur%rowtype;  8  BEGIN  9    OPEN cur; 10    LOOP 11      FETCH cur INTO rec; 12      EXIT 13    WHEN cur%notfound; 14      DBMS_UTILITY.comma_to_table ( 15      list => rec.val, tablen => l_tablen, tab => l_tab); 16      FOR i IN 1 .. l_tablen 17      LOOP 18        DBMS_OUTPUT.put_line(i || ' : ' || trim(l_tab(i))); 19      END LOOP; 20    END LOOP; 21    CLOSE cur; 22  END; 23  /1 : word12 : word23 : word34 : word45 : word56 : word6PL/SQL procedure successfully completed.SQL>

UPDATE As @ruudvan points out, there are restrictions for using COMMA_TO_TABLE like it won't work if you have keywords as delimited string like IS,AS etc.

To overcome the restrictions of COMMA_TO_TABLE, there are many other ways of doing it, see How to split comma delimited string into rows in Oracle

For example, you could use REGULAR EXPRESSION as shown below:

Test case

SQL> CREATE OR REPLACE  2  PROCEDURE Get_Query(  3      v_company IN VARCHAR2 )  4  IS  5  BEGIN  6      7    FOR i IN  8    (SELECT level,  9      trim(regexp_substr(v_company, '[^,]+', 1, LEVEL)) str 10    FROM dual 11      CONNECT BY regexp_substr(v_company , '[^,]+', 1, LEVEL) IS NOT NULL 12    ) 13    LOOP 14      -- do something 15      dbms_output.put_line('Company code no.'||i.level||' = '||i.str); 16    END LOOP; 17  END; 18  /Procedure created.SQL> sho errNo errors.

Let's check:

SQL> set serveroutput onSQL> EXEC get_query('COMP1,COMP2,COMP3,COMP4');Company code no.1 = COMP1Company code no.2 = COMP2Company code no.3 = COMP3Company code no.4 = COMP4PL/SQL procedure successfully completed.SQL>

This use of regular expressions converts the input string into a stream of tokens which can be processed like the result set of any other SELECT statement.

PROCEDURE Get_Query(  v_company IN VARCHAR2,) IS    sqlstr VARCHAR2(32767);BEGIN    for rec in (select distinct regexp_substr(v_company, '[^,]+', 1, level) as ctry    from dual    connect by level <= regexp_count (v_company, '[,]')  +1   loop        do_something ( rec.ctry );    end loop;END;