How to use SQL 'IN' (or 'ANY') operator with VARRAY in PL/SQL

Question

My .NET code is currently using ODP.NET to call a stored procedure many times to operate on various rows in many tables. The .NET code has an array of the rows to change. Only one parameter changes in each call, and I'd like to pass the array from .NET to the PL/SQL to operate on multiple rows (the number of rows will change).

I've successfully passed an array from .NET to PL/SQL using:

type number_arr is table of number(10) index by pls_integer;
PROCEDURE "BLAH" (foo IN number_arr);

Note that I believe number_arr is called a VARRAY, but I'm not positive about that, and if someone wants to correct me, please do (as a comment), but this might be contributing to my confusion.

But now, in the PL/SQL, I have many update statements that used to look like:

UPDATE t SET a = b WHERE a = foo;

when foo wasn't an array. I now want to write:

UPDATE t SET a = b WHERE a IN (foo);

But this syntax doesn't seem to work. And I've been unable to find an example for Oracle that combines use of VARRAY and 'IN' (or 'ANY', etc.). And I've seen some answers for how to do this with SQL Server, but I'm not sure how to translate that to Oracle.

Of course, if there is some other way to get the array from .NET to the stored procedure to do this, that would also answer my question. I'm looking to gain efficiency with the IN, so something that itterates over the array in PL/SQL (to call the UPDATE statements separately) probably won't help.

Answer 1

The array you're using is an associative array, not a varray. varrays and nested tables can be used in SQL, but associative arrays cannot. However, since you're trying to do this in PL/SQL in the first place, you can use a bulk bind (which will work with an associative array):

PROCEDURE BLAH (foo IN number_arr) is
i number;
begin
   forall i in foo.first .. foo.last
   UPDATE t SET a = b WHERE a = foo(i);
end blah;

If you created number_arr as a varray in the database instead of an associative array, you could use the table function instead:

create type number_arr as varray(10) of number;

CREATE PROCEDURE BLAH (foo IN number_arr) is
begin
   UPDATE t SET a = b WHERE a in (select * from table(foo));
end blah;

Note that, in this case, the type has to be defined in the database, not in your package. Also, this method won't necessarily be any faster than using forall.