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

User · Answer

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     gt  iv raw                                tablen   gt  ln len                                tab      gt  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

User · Answer

I am not sure if this fits your oracle version  On my 10g I can use pipelined table functions   set serveroutput on  create type number list as table of number   -- since you want this solution create or replace function split csv  i csv varchar2  return number list pipelined    is      mystring varchar2 2000    i csv    begin     for r in       select regexp substr mystring         1 level  element         from dual      connect by level  lt   length regexp replace mystring             1           loop       --dbms output put line r element         pipe row to number r element   999999 99         end loop    end     insert into foo select column a column b from     select column value column a  rownum rn from table split csv  0 75  0 64  0 56  0 45     a     select column value column b  rownum rn from table split csv  0 25  0 5  0 65  0 8     b  where a rn   b rn

User · Answer

Many good solutions have been provided already  However  if he text is provided in a very simple comma delimited format or similar  and speed is of importance  then I have for you a solution with a TABLE function  in PL SQL   I have also provided a rundown of some other solutions   Please see more on the Blog Entry on Parsing a CSV into multiple columns

User · Answer

CREATE OR REPLACE PROCEDURE insert into      p errcode        OUT   NUMBER     p errmesg        OUT   VARCHAR2     p rowsaffected   OUT   INTEGER   AS    v param0   VARCHAR2  30      0 25 2 25 33 689  abc  99      v param1   VARCHAR2  30      2 65 66 32  abc-def  21 5   BEGIN    FOR i IN  SELECT COLUMN VALUE                FROM TABLE  SPLIT  v param0            LOOP       INSERT INTO tempo                    col1                                VALUES  i COLUMN VALUE                         END LOOP      FOR i IN  SELECT COLUMN VALUE                FROM TABLE  SPLIT  v param1            LOOP       INSERT INTO tempo                    col2                                VALUES  i COLUMN VALUE                         END LOOP  END

User · Answer

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  lt  gt  v tbl2 COUNT    THEN       raise application error num   gt  -20001  msg   gt   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

User · Answer

create or replace procedure pro ss v str varchar2  as v str1 varchar2 100    v comma pos number    0      v start pos number    1  begin                  loop             v comma pos    instr v str     v start pos          if  v comma pos   0 then            v str1    substr v str v start pos           dbms output put line v str1             exit        end if          v str1    substr v str v start pos  v comma pos - v start pos         dbms output put line v str1              v start pos    v comma pos   1          end loop   end     call pro ss  aa bb cc dd ee ff gg hh ii jj         outout  aa bb cc dd ee ff gg hh ii jj

User · Answer

As for the connect by use case  this approach should work for you   select regexp substr  SMITH ALLEN WARD JONES           1  level  from dual connect by regexp substr  SMITH ALLEN WARD JONES            1  level  is not null

User · Answer

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

[oracle] Splitting comma separated string in a PL/SQL stored proc

Examples related to oracle

Examples related to plsql

Examples related to tokenize