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!

http://stackoverflow.com/questions/11508240/how-can-i-supply-a-listint-to-a-sql-parameter — Habib, Jun 19 '14 at 13:43
http://stackoverflow.com/questions/4032006/passing-an-array-from-net-application-to-oracle-stored-procedure?rq=1 — Daniel Kelley, Jun 19 '14 at 13:56
What ADO.NET provider are you using? You could pass the numbers as an array, if the provider supports it. Oracle does support array parameters, check [this SO question](http://stackoverflow.com/questions/15515772/array-in-in-clause-oracle-plsql) on how to pass a list of values and due a proper `IN` query — Panagiotis Kanavos, Jun 19 '14 at 13:56
possible duplicate of [OracleParameter and IN Clause](http://stackoverflow.com/questions/541466/oracleparameter-and-in-clause) — Panagiotis Kanavos, Jun 19 '14 at 14:06

score 5 · Accepted Answer · edited May 23 '17 at 12:08

5

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;

edited May 23 '17 at 12:08

Community

1
1

answered Jun 19 '14 at 14:10

Panagiotis Kanavos

120,703
13
188
236

I think for proper operation you have to add `param.Size = boxids.Length;` befrore you execute the command. – Wernfried Domscheit Jun 20 '14 at 13:08
@Wernfried you also have to actually set the `.Value` property, add the parameter to the `Parameters` collection, write a command text with the same number and names of parameters as those you pass - without looking at the code we can't really help – Panagiotis Kanavos Jun 20 '14 at 15:14
@gauravthakur post a new question with your code, schema and specific error. It's also rather hard to understand what you mean `I don't want to create it at schema level`. At what level do you want to define the type? Don't answer here, post a question explaining *why* you don't want to create a new type or table – Panagiotis Kanavos Feb 28 '18 at 08:50
what did you do for the Value problem? @gs_developer_user3605534 – masoud vali May 23 '18 at 09:05
@masoudvali SO isn't a discussion forum and comments to answers are comments to answers, not replies to others. It's not Twitter either, you can't notify someone by putting their name at the *end* of a comment. What `Value problem`? If you pass the wrong number of arguments, you passed the wrong number of arguments. If you have a question, post a question – Panagiotis Kanavos May 23 '18 at 09:10
@masoudvali did the same what? There was no question, just a *comment* without information. As for the error message itself, it could simply mean there was a typo in the parameter names. Or the stored procedure was called with [more or fewer parameters than it actually has](https://stackoverflow.com/questions/16737141/wrong-number-or-types-of-arguments-in-call-to-my-procedure). Or a wrong parameter type. Perhaps an *unrelated* parameter – Panagiotis Kanavos May 23 '18 at 09:38

score 1 · Answer 2 · edited Jun 19 '14 at 14:17

I'd create a new procedure, designed to handle a list of values. An efficient approach would be to load the multiple values into a global temp table, using a bulk insert, and then have the procedure update using a join to the GTT.

A notional example would look like this:

OracleTransaction trans = conn.BeginTransaction(IsolationLevel.RepeatableRead);
OracleCommand cmd = new OracleCommand(insertSql, conn, trans);
cmd.Parameters.Add(new OracleParameter("BOX_ID", OracleDbType.Number));
cmd.Parameters[0].Value = listOfBoxIds;   // int[] listOfBoxIds;
cmd.ExecuteArray();

OracleCommand cmd2 = new OracleCommand(storedProc, conn, trans);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.ExecuteNonQuery();

trans.Commit();

score 0 · Answer 3 · edited May 23 '17 at 11:44

I understand your concern - the round trips will be taxing.

Unfortunately I don't have anything to test, but you can try Oracle bulk updates using ODP.NET or

-- edit1: go with Panagiotis Kanavos's answer if your provider supports it, else check below --

-- edit12 as highlighted by Wernfried, long is deprecated. Another thing consider is max length varchar2: it doesn't scale on a very big set. Use the one below as the last resort. --

changing your stored procedure to accept string

implement string_2_list in asktom.oracle.com.

create or replace type myTableType as table of varchar2 (255);

create or replace function in_list( p_string in varchar2 ) return myTableType
 as
     l_string        long default p_string || ',';
     l_data          myTableType := myTableType();
     n               number;
 begin
   loop
       exit when l_string is null;
       n := instr( l_string, ',' );
       l_data.extend;
       l_data(l_data.count) := 
             ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
       l_string := substr( l_string, n+1 );
  end loop;

  return l_data;
end;

Above is early variant and splice to varchar2, but if you read more (including other threads) at that site you'll find more advanced variants (optimized, better exception handling)

Oracle has multiple collection types that allow you to pass a list of arguments without resorting to strings. — Panagiotis Kanavos, Jun 19 '14 at 14:15
Oracle datatype `LONG` is deprected for ages! You should not use it in any new written code. — Wernfried Domscheit, Jun 20 '14 at 13:25
@Wernfried: below the code I've suggested to read the original article further for more advanced variant. I gave the initial version simply because link-only answer is not recommended in SO. — GangBunTu, Jun 21 '14 at 02:00

score 0 · Answer 4 · answered Jun 20 '14 at 13:22

Your PL/SQL block may look like this one:

CREATE OR REPLACE PACKAGE YOUR_PACKAGE AS
   TYPE TArrayOfNumber IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   PROCEDURE Update_Boxes(boxes IN TArrayOfNumber );

END YOUR_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY YOUR_PACKAGE AS

PROCEDURE Update_Boxes(boxes IN TArrayOfNumber) is
BEGIN
   FORALL i IN INDICES OF boxes 
   update boxes  
   set location = boxes(i) 
   where boxid = ...;
END Update_Boxes;

END YOUR_PACKAGE;

The C# code you get already in answer from Panagiotis Kanavos

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

4 Answers4

Linked

Related