0

I'm trying to send a string like this to Oracle Stored Procedure: this is an ugly way, but I couldn't find any other way.

1,2,3;4,5,6;7,8,9

or

1,2,3,4;5,6,7,8

and I want to parse it to a specific table and return it so I can use it with the join or merge functions in a way that the ',' is the column separator and the ';' is the row separator.

assuming that the string is in the correct syntax

my code right now:

Procedure SplitStringToTable
(
    p_stringToSplitToRows in varchar2,
    p_stringToSplitToColumns in varchar2,
    p_rowDelimeter in varchar2,
    p_columnDelimeter in varchar2,
    p_columnAmount in number,
    cur_out out CurType

)
IS

    selectArgument varchar(20);
    i number;
    type t_temp is table of TEMPHELP%rowtype; -- this is a table with one column
    myTemp t_temp;

BEGIN
    select * bulk collect into myTemp from
    (
        select * from
        (
            select regexp_substr(p_stringToSplitToRows, '[^'+p_rowDelimeter+']+',1,level) as a from dual
            connect by regexp_substr(p_stringToSplitToRows, '[^'+p_rowDelimeter+']+',1,level) is not null
        )
    )
    selectArgument :='';
    i:=1;
    loop
--ERROR HERE\
        selectArgument:= selectArgument+' '+regexp_substr(myTemp , '[^'+p_columnDelimeter +']+',1,i); 
--ERROR HERE/
        if i=p_columnAmount then
             selectArgument:= selectArgument;
        else
             selectArgument:=selectArgument+',';
        end if;
        if (i>p_columnAmount) then 
            exit;
        else
            i:=i+1;
        end if;
    end loop;


    open cur_out for
    select selectArgument
    from 
    (
        select regexp_substr(p_stringToSplitToRows, '[^'+p_rowDelimeter+']+',1,level) as a from dual
        connect by regexp_substr(p_stringToSplitToRows, '[^'+p_rowDelimeter+']+',1,level) is not null
    )
END;

I've tested the regexp with this string: '1,2,3;4,5,6;7,8,9' , and this way it worked:

select regexp_substr(A, '[^,]+',1,1),regexp_substr(A, '[^,]+',1,2),regexp_substr(A, '[^,]+',1,3)
        from 
        (
            select regexp_substr('1,2,3;4,5,6;7,8,9', '[^;]+',1,level) as A from dual
            connect by regexp_substr('1,2,3;4,5,6;7,8,9', '[^;]+',1,level) is not null
        )

the problem I get now is

wrong number or types of arguments in call to 'REGEXP_SUBSTR'

I'm not sure that this thing is even possible,

the main purpose is a way to send a DataTable from C# to an Oracle Stored Procedure.

If anybody can suggest a better way to do so. I would be glad or just help me fix my code

Dor Lugasi-Gal
  • 1,430
  • 13
  • 35
  • The error says everything, just check the position or error. – Renatas M. Nov 20 '18 at 10:05
  • A search on "oracle split delimited string" here or on the web didn't work? Strange. E.g. https://lalitkumarb.wordpress.com/2014/12/02/split-comma-delimited-string-into-rows-in-oracle/ – dnoeth Nov 20 '18 at 10:18
  • That's not how you pass parameters to a procedure. Pas an refcursor or array https://stackoverflow.com/questions/40279185/passing-array-to-oracle-procedure-from-c-sharp – Kaushik Nayak Nov 20 '18 at 10:28
  • dnoeth, this is a single split, if you would read all the question you would see i already use this in my code. i'm trying to get a table – Dor Lugasi-Gal Nov 20 '18 at 11:12
  • Reniuz, thanks for the suggestion. Ive posted the question AFTER i checked the error – Dor Lugasi-Gal Nov 20 '18 at 11:13
  • Kaushik Nayak, the procedure is inside a package, so thats the way it is working for all my procedures curType is a refcursor – Dor Lugasi-Gal Nov 20 '18 at 11:14

0 Answers0