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

[Guest]

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

Thank you in advance

Nelson

 

[William Ryan eMVP]

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

 

[Phillip Hamlyn]

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