Splitting comma separated string in a PL/SQL stored proc

Question

I've CSV string 100.01,200.02,300.03 which I need to pass to a PL/SQL stored procedure in Oracle. Inside the proc,I need to insert these values in a Number column in the table.

For this, I got a working approach from over here:

How to best split csv strings in oracle 9i

[2) Using SQL's connect by level.].

Now,I've another requirement. I need to pass 2 CSV strings[equal in length] as input to PL/SQL stored proc.And, I need to break this string and insert each value from two CSV strings into two different columns in the table.Could you please let me know how to go about it?

Example of CSV inputs: mystring varchar2(2000):='0.75, 0.64, 0.56, 0.45';

myAmount varchar2(2000):= '0.25, 0.5, 0.65, 0.8';

myString values would go into Column A and myAmount values into Column B in the table.

Could you please let me know how to achieve this?

Thanks.

Answer 1

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;