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

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)