Splitting comma separated string in a PL/SQL stored proc

This should do what you are looking for.. It assumes your list will always be just numbers. If that is not the case, just change the references to DBMS_SQL.NUMBER_TABLE to a table type that works for all of your data:

CREATE OR REPLACE PROCEDURE insert_from_lists(    list1_in IN VARCHAR2,    list2_in IN VARCHAR2,    delimiter_in IN VARCHAR2 := ',')IS     v_tbl1 DBMS_SQL.NUMBER_TABLE;    v_tbl2 DBMS_SQL.NUMBER_TABLE;    FUNCTION list_to_tbl    (        list_in IN VARCHAR2    )    RETURN DBMS_SQL.NUMBER_TABLE    IS        v_retval DBMS_SQL.NUMBER_TABLE;    BEGIN        IF list_in is not null        THEN            /*            || Use lengths loop through the list the correct amount of times,            || and substr to get only the correct item for that row            */            FOR i in 1 .. length(list_in)-length(replace(list_in,delimiter_in,''))+1            LOOP                /*                || Set the row = next item in the list                */                v_retval(i) :=                         substr (                            delimiter_in||list_in||delimiter_in,                            instr(delimiter_in||list_in||delimiter_in, delimiter_in, 1, i  ) + 1,                            instr (delimiter_in||list_in||delimiter_in, delimiter_in, 1, i+1) - instr (delimiter_in||list_in||delimiter_in, delimiter_in, 1, i) -1                        );            END LOOP;        END IF;        RETURN v_retval;    END list_to_tbl;BEGIN    -- Put lists into collections   v_tbl1 := list_to_tbl(list1_in);   v_tbl2 := list_to_tbl(list2_in);   IF v_tbl1.COUNT <> v_tbl2.COUNT   THEN      raise_application_error(num => -20001, msg => 'Length of lists do not match');   END IF;   -- Bulk insert from collections   FORALL i IN INDICES OF v_tbl1      insert into tmp (a, b)      values (v_tbl1(i), v_tbl2(i));END insert_from_lists; 

I use apex_util.string_to_table to parse strings, but you can use a different parser if you wish. Then you can insert the data as in this example:

declare  myString varchar2(2000) :='0.75, 0.64, 0.56, 0.45';  myAmount varchar2(2000) :='0.25, 0.5, 0.65, 0.8';  v_array1 apex_application_global.vc_arr2;  v_array2 apex_application_global.vc_arr2;begin  v_array1 := apex_util.string_to_table(myString, ', ');  v_array2 := apex_util.string_to_table(myAmount, ', ');  forall i in 1..v_array1.count     insert into mytable (a, b) values (v_array1(i), v_array2(i));end;

Apex_util is available from Oracle 10G onwards. Prior to this it was called htmldb_util and was not installed by default. If you can't use that you could use the string parser I wrote many years ago and posted here.

Here is a good solution:

FUNCTION comma_to_table(iv_raw IN VARCHAR2) RETURN dbms_utility.lname_array IS   ltab_lname dbms_utility.lname_array;   ln_len     BINARY_INTEGER;BEGIN   dbms_utility.comma_to_table(list   => iv_raw                              ,tablen => ln_len                              ,tab    => ltab_lname);   FOR i IN 1 .. ln_len LOOP      dbms_output.put_line('element ' || i || ' is ' || ltab_lname(i));   END LOOP;   RETURN ltab_lname;END;

Source: CSV - comma separated values - and PL/SQL (link no longer valid)