Unanswered: Passing a single value parameter along with a 2nd parametr that is an array of values
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
procedure (id_in in number, names_in in varchar2, addresses_in in varchar2) etc
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.
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.
procedure function1 (id_in in number, names_in in varchar2, addresses_in in varchar2)
select suburb_name into suburb from table1 where id = id_in;
insert into table2
(name, address, suburb_name)
procedure function2(names_in in varchar2, addresses_in in varchar2)
insert into table2
Re: Passing a single value parameter along with a 2nd parametr that is an array of values
Well, I don't know how .NET would handle this, but this is how to use arrays in PL/SQL:
create type names_array_t is table of varchar2(30);
create type addr_array_t is table of varchar2(40);
procedure function1 (id_in in number, names_in in names_array_t, addresses_in in addr_array_t)
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.