5

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.

Allan
  • 17,141
  • 4
  • 52
  • 69
Andy Jacobs
  • 933
  • 3
  • 10
  • 18

1 Answers1

8

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.

Allan
  • 17,141
  • 4
  • 52
  • 69
  • I believe that in your last code snippet, `table(foo)` would need to be `table(cast(foo as number_arr))`. But overall, good answer. – Dave Costa Jul 16 '11 at 03:29
  • you're right! I think that at some point in the past the CAST was required, but testing in 10.2.0.4 shows it's not now. – Dave Costa Jul 18 '11 at 13:26
  • Thank you for your answer. Knowing that the forall will be at least as fast helps, so I won't mind writing the loop for many of my cases. However, I did simplify my question from my actual code, and there are still places where I would like to use an associative array with an "IN" or "ANY" operator. Is this possible? – Andy Jacobs Jul 24 '11 at 20:35
  • @Andy: No, you can't use an associative array with `in` or `any`. Associative arrays are PL/SQL only, so they can never be used in SQL (even if executed within PL/SQL). – Allan Jul 26 '11 at 11:30