How to use Parameter List of integers for Oracle w

Question

I am trying to re-write some code to use Dapper so I can easily use parameters. I am trying to execute an UPDATE statement on an Oracle database. A list of IDs to UPDATE is passed in as List<int> as parameter. I want to update a field for each of the IDs passed in. The following is what I have:

OracleConnection connection = ... // set earlier

public int IncreaseProcessCount(List<int> ids)
{
    var rowsAffected = connection.Execute(@"UPDATE TABLE SET PROCESSED_COUNT = PROCESSED_COUNT + 1 WHERE ID IN @ids", new { ids });
    return rowsAffected;
}

Before using Dapper, the execution statement was working just fine. Now I am getting following error:

ORA-00936: missing expression.

My current solution is based on below posts:

Dapper query with list of parameters and Performing Inserts and Updates with Dapper

Answer 1

I don't know Dapper (never even heard of it) so I apologize if this is a nonsense; however, in this:

WHERE ID IN @ids

IN suggests that Oracle expects a list of elements enclosed into parentheses. So, what is @ids really? If it is a single value, parentheses aren't necessary. But, if there are two or more of them (though, up to 1000), they have to be separated by a comma and - as I said - enclosed into parentheses, such as (1, 2, 8). I suppose you've already separated them - now try to add () and see what happens.

Answer 2

I am not sure if this is Oracle specific issue as I never worked with Oracle + Dapper combination. But I strongly suspect the way you are passing parameter is a problem. The exception "missing expression" is saying the same thing.

Refer modification of your code below:

public int IncreaseProcessCount(int[] ids)
{
    var rowsAffected = connection.Execute(@"UPDATE TABLE SET PROCESSED_COUNT = PROCESSED_COUNT + 1 WHERE ID IN :ids", new { ids });
    return rowsAffected;
}

There are following differences:

1. Use of ":ids" instead of "@ids". I strongly suspect this is an issue because Oracle expects : instead of @ for parameters.
2. Use of int[] instead of List<int>. This should not be an issue because Dapper supports IEnumerable for parameter list; so List should be OK. You have already tried this (as you mentioned in comments) without success.

Refer this question for Dapper with IN clause using Parameter List. Here is another resource.

Edit (for comments):

The core of the problem was use of ":ids" which was correctly included in my answer. I just corrected syntax error in the code above.

Also, I generally use DynamicParameters. Actually, it was not an issue in this case, so I removed that part which was present in first version of my answer. Anyway, following is the code with DynamicParameters which should work equally.

public int IncreaseProcessCount(int[] ids)
{
    var param = new DynamicParameters();
    param.Add(":ids", ids);

    var rowsAffected = connection.Execute(@"UPDATE TABLE SET PROCESSED_COUNT = PROCESSED_COUNT + 1 WHERE ID IN :ids", param);
    return rowsAffected;
}

Answer 3

Based off Amit's answer, below is what I finally got to work. I had to wrap the collection being passed in with an anonymous object.

connection.Execute("UPDATE TABLE SET PROCESSED_COUNT = PROCESSED_COUNT+ 1 WHERE ID IN :ids",
                    new { ids });