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
