Mixing
trying to parse a string to a table in Oracle
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
c# sql oracle datatable
asked Nov 20 '18 at 9:59
Dor LugasiDor Lugasi
316214
|
show 1 more comment
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
c# sql oracle datatable
asked Nov 20 '18 at 9:59
Dor LugasiDor Lugasi
316214
The error says everything, just check the position or error.
– Reniuz
Nov 20 '18 at 10:05
A search on "oracle split delimited string" here or on the web didn't work? Strange. E.g. lalitkumarb.wordpress.com/2014/12/02/…
– dnoeth
Nov 20 '18 at 10:18
That's not how you pass parameters to a procedure. Pas an refcursor or array stackoverflow.com/questions/40279185/…
– 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
Nov 20 '18 at 11:12
Reniuz, thanks for the suggestion. Ive posted the question AFTER i checked the error
– Dor Lugasi
Nov 20 '18 at 11:13
|
show 1 more comment
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
c# sql oracle datatable
asked Nov 20 '18 at 9:59
Dor LugasiDor Lugasi
316214
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
c# sql oracle datatable
c# sql oracle datatable
asked Nov 20 '18 at 9:59
Dor LugasiDor Lugasi
316214
asked Nov 20 '18 at 9:59
Dor LugasiDor Lugasi
316214
asked Nov 20 '18 at 9:59
Dor LugasiDor Lugasi
316214
asked Nov 20 '18 at 9:59
Dor LugasiDor Lugasi
316214
asked Nov 20 '18 at 9:59
Dor LugasiDor Lugasi
316214
316214
The error says everything, just check the position or error.
– Reniuz
Nov 20 '18 at 10:05
A search on "oracle split delimited string" here or on the web didn't work? Strange. E.g. lalitkumarb.wordpress.com/2014/12/02/…
– dnoeth
Nov 20 '18 at 10:18
That's not how you pass parameters to a procedure. Pas an refcursor or array stackoverflow.com/questions/40279185/…
– 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
Nov 20 '18 at 11:12
Reniuz, thanks for the suggestion. Ive posted the question AFTER i checked the error
– Dor Lugasi
Nov 20 '18 at 11:13
|
show 1 more comment
The error says everything, just check the position or error.
– Reniuz
Nov 20 '18 at 10:05
A search on "oracle split delimited string" here or on the web didn't work? Strange. E.g. lalitkumarb.wordpress.com/2014/12/02/…
– dnoeth
Nov 20 '18 at 10:18
That's not how you pass parameters to a procedure. Pas an refcursor or array stackoverflow.com/questions/40279185/…
– 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
Nov 20 '18 at 11:12
Reniuz, thanks for the suggestion. Ive posted the question AFTER i checked the error
– Dor Lugasi
Nov 20 '18 at 11:13
The error says everything, just check the position or error.
– Reniuz
Nov 20 '18 at 10:05
The error says everything, just check the position or error.
– Reniuz
Nov 20 '18 at 10:05
A search on "oracle split delimited string" here or on the web didn't work? Strange. E.g. lalitkumarb.wordpress.com/2014/12/02/…
– dnoeth
Nov 20 '18 at 10:18
A search on "oracle split delimited string" here or on the web didn't work? Strange. E.g. lalitkumarb.wordpress.com/2014/12/02/…
– dnoeth
Nov 20 '18 at 10:18
That's not how you pass parameters to a procedure. Pas an refcursor or array stackoverflow.com/questions/40279185/…
– Kaushik Nayak
Nov 20 '18 at 10:28
That's not how you pass parameters to a procedure. Pas an refcursor or array stackoverflow.com/questions/40279185/…
– 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
Nov 20 '18 at 11:12
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
Nov 20 '18 at 11:12
Reniuz, thanks for the suggestion. Ive posted the question AFTER i checked the error
– Dor Lugasi
Nov 20 '18 at 11:13
Reniuz, thanks for the suggestion. Ive posted the question AFTER i checked the error
– Dor Lugasi
Nov 20 '18 at 11:13
|
show 1 more comment
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53390450%2ftrying-to-parse-a-string-to-a-table-in-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53390450%2ftrying-to-parse-a-string-to-a-table-in-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
The error says everything, just check the position or error.
– Reniuz
Nov 20 '18 at 10:05
A search on "oracle split delimited string" here or on the web didn't work? Strange. E.g. lalitkumarb.wordpress.com/2014/12/02/…
– dnoeth
Nov 20 '18 at 10:18
That's not how you pass parameters to a procedure. Pas an refcursor or array stackoverflow.com/questions/40279185/…
– 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
Nov 20 '18 at 11:12
Reniuz, thanks for the suggestion. Ive posted the question AFTER i checked the error
– Dor Lugasi
Nov 20 '18 at 11:13