sql - Pass a list of integers from C# into Oracle stored procedure

Question

I have a oracle stored procedure which updates a table with the following statement.

update boxes 
set    location = 'some value'
where  boxid = passed value

I have a page where the user selects 100+ boxes and updates them with a new location value. Currently, I have to call the stored procedure 100+ times to update each box(by passing a boxid each time).

I want to know how I can pass a list of boxids from C# into the stored procedure so that I have to call the stored procedure just one time.

I am hoping to use a where in(boxids) kind of where clause in the update statement.

Please let know how can I achieve this. Thanks in advance!

Answer 1

Oracle allows you to pass arrays of values as parameters. Borrowing from this SO question and this one you can define an INT_ARRAY type like this:

create or replace type CHAR_ARRAY as table of INTEGER;

Then define your stored procedure as:

CREATE OR REPLACE PROCEDURE product_search(
        ...
        myIds IN CHAR_ARRAY,
        ...)
AS  
    SELECT ...
    ...
    WHERE SomeIdField IN (Select column_value FROM TABLE(myIds))
    ...

You can then pass the list of values by setting the OracleParameter.CollectionType property like this:

OracleParameter param = new OracleParameter();
param.OracleDbType = OracleDbType.Int32;
param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;