Passing a single value parameter along with a 2nd parametr that is an array of values

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Passing a single value parameter along with a 2nd parametr that is an array of values

Hi all

I am using ODP.NET (oracle db provider for .NET) with Oracle Database.
I don't know whether my problem can be solved by manipulating the .NET program or the PL/SQL procedures. So I will try to explain my problem.

Basically I will need to pass single value parameter along with other parameters that may is an array of values. for example

Where names_in and addresses_in are arrays of names and addresses and id_in is a single value. I will need to perform insertion of names and addresses into a table based on the id_in parameter.

another word

i first do a select based on the id to retrieve extra information. based on this information, i will insert the names and addresses along with other values retrieved from the select statement. function1 has an extra id_in, where as function2 doesn't have a single value parameter id_in. function 1 will not work correctly because id_in is treated as an array parameter along with the other array parameters thus returning a cast error. function2 works fine because all the parameters are arrays.

The question is thus, how do I pass parameters to a procedure using ODP.NET in such a way that the parameters may be a mix of single value and array parameters. There is a way to get around this by not using procedures, instead directly use "insert into .... " as the CommandText, this works but the logic that i am implementing is rather complex and requires a great deal of processing which i think can only be done within a procedure.

I don't know how .NET could populate the arrays before calling the procedure, but I imagine it is possible. If it were not possible, you could avoid it by building up the arrays inside a PL/SQL package with calls like this:

I have resorted to doing multiple calls to insert the array of information I wanted to pass in a single go.
Couldn't find any documentation on how it could be done. Hopefully the next version of Oracle Provider for MS .NET would fix this deficiency.