469,610 Members | 1,844 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

How to pass an array from .net to oracle stored procedure

Hi All
Does anyone knows how to pass an array from .net application to oracle stored procedure

Thank you in advance

Nelson
Nov 22 '05 #1
4 10549
Nelson:

I wrote this for SQL Server but the logic is similar.
http://www.knowdotnet.com/articles/temptables.html
Basically, create a string with the array putting a delimitter in between
the values. Pass that string as a Parameter. Parse the values in between
the delimmiter and insert them into a Temp table and use the temp table to
do whatever you want.

Depending on what you want to do, you may just need to simply parse the
values within a loop but I'm not sure what your end game is.

HTH,

Bill
"Nelson Xu" <ni**********@litton.c-bass.com> wrote in message
news:F9**********************************@microsof t.com...
Hi All,
Does anyone knows how to pass an array from .net application to oracle stored procedure.
Thank you in advance !

Nelson

Nov 22 '05 #2
Nelson:

I wrote this for SQL Server but the logic is similar.
http://www.knowdotnet.com/articles/temptables.html
Basically, create a string with the array putting a delimitter in between
the values. Pass that string as a Parameter. Parse the values in between
the delimmiter and insert them into a Temp table and use the temp table to
do whatever you want.

Depending on what you want to do, you may just need to simply parse the
values within a loop but I'm not sure what your end game is.

HTH,

Bill
"Nelson Xu" <ni**********@litton.c-bass.com> wrote in message
news:F9**********************************@microsof t.com...
Hi All,
Does anyone knows how to pass an array from .net application to oracle stored procedure.
Thank you in advance !

Nelson

Nov 22 '05 #3
William,

I found that parsing the parameter into SQL Server parseable XML using a
similar function worked a treat, and of course the parsing of the XML into a
temp table is part and parcel of SQL Server 2000. It works a treat in
reducing the chattiness of the average complex database transaction, say of
saving a hierarchical object into several database tables in one push. I
remember the SQL Server docs have something to say on the lines of "network
round tripping is found to be at the root of many SQL Server based
application performance issues" so I figure the extra cost of the parsing on
the SQL Server is worth it. Beware though the cost of parsing out XML is
exponential - if you end up parsing out more than 4000 records you're likely
to notice a dramatic increase in time taken by SQL Server to do this job, so
we programmatically limit the number of pseudo-records being passed in the
XML parameter.

Of course, in Oracle a PL-SQL procedure can accept a parameter array, but I
don't know how ADO exposes this (if it does at all - the previous posted
might need to use the proprietary Oracle connection libraries to achieve
this).

Phillip
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:O$**************@TK2MSFTNGP12.phx.gbl...
Nelson:

I wrote this for SQL Server but the logic is similar.
http://www.knowdotnet.com/articles/temptables.html
Basically, create a string with the array putting a delimitter in between
the values. Pass that string as a Parameter. Parse the values in between
the delimmiter and insert them into a Temp table and use the temp table to
do whatever you want.

Depending on what you want to do, you may just need to simply parse the
values within a loop but I'm not sure what your end game is.

HTH,

Bill
"Nelson Xu" <ni**********@litton.c-bass.com> wrote in message
news:F9**********************************@microsof t.com...
Hi All,
Does anyone knows how to pass an array from .net application to oracle

stored procedure.

Thank you in advance !

Nelson


Nov 22 '05 #4
William,

I found that parsing the parameter into SQL Server parseable XML using a
similar function worked a treat, and of course the parsing of the XML into a
temp table is part and parcel of SQL Server 2000. It works a treat in
reducing the chattiness of the average complex database transaction, say of
saving a hierarchical object into several database tables in one push. I
remember the SQL Server docs have something to say on the lines of "network
round tripping is found to be at the root of many SQL Server based
application performance issues" so I figure the extra cost of the parsing on
the SQL Server is worth it. Beware though the cost of parsing out XML is
exponential - if you end up parsing out more than 4000 records you're likely
to notice a dramatic increase in time taken by SQL Server to do this job, so
we programmatically limit the number of pseudo-records being passed in the
XML parameter.

Of course, in Oracle a PL-SQL procedure can accept a parameter array, but I
don't know how ADO exposes this (if it does at all - the previous posted
might need to use the proprietary Oracle connection libraries to achieve
this).

Phillip
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:O$**************@TK2MSFTNGP12.phx.gbl...
Nelson:

I wrote this for SQL Server but the logic is similar.
http://www.knowdotnet.com/articles/temptables.html
Basically, create a string with the array putting a delimitter in between
the values. Pass that string as a Parameter. Parse the values in between
the delimmiter and insert them into a Temp table and use the temp table to
do whatever you want.

Depending on what you want to do, you may just need to simply parse the
values within a loop but I'm not sure what your end game is.

HTH,

Bill
"Nelson Xu" <ni**********@litton.c-bass.com> wrote in message
news:F9**********************************@microsof t.com...
Hi All,
Does anyone knows how to pass an array from .net application to oracle

stored procedure.

Thank you in advance !

Nelson


Nov 22 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by kristoff plasun | last post: by
4 posts views Thread by Michael Trosen | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.