1

Have a very simple query which perfectly works on oracle db side, but trying to implement it using .net and have following issues below, here's code example:

var cmd = new OracleCommand
{
    CommandText = "SELECT * FROM TESTTABLE WHERE ID IN (:1)"
};

cmd.ArrayBindCount = values.Length; // an array of integers boxed to object[]

cmd.Parameters.Add(new OracleParameter
    {
        OracleDbType = OracleDbType.Array,
        Value = values // object array
    });

var reader = await cmd.ExecuteReaderAsync();

Using this approach I've an exception - Invalid parameter binding If I would replace OracleDbType to OracleDbType = OracleDbType.Decimal then I've following exception - ORA-03146: invalid buffer length for TTC field.

Help me please to identify what I'm missing here?

Improve this question
2
  • Can u ensure that your values array is an array of integers? Commented Sep 19, 2023 at 16:12
  • @YasinARLI Did that before creating post here, so I've also tried to pass directly an array of integers, for ex, 5 values and 5 as count I've specified inside ArrayBindCount, same issues as it mentioned above. Commented Sep 19, 2023 at 20:12

1 Answer 1

Reset to default
1

Help me please to identify what I'm missing here?

SELECT * FROM TESTTABLE WHERE ID IN (1, 2, 3)

Will work.

SELECT * FROM TESTTABLE WHERE ID IN ( :bind_variable_containing_collection_type )

Will not work because:

  1. A bind variable contains a single value (regardless of whether that single value is a scalar or a UDT containing an array) and the IN clause will compare the left-operand with each of the right-operands to see if they are equal; since there is only a single value in the IN clause you are checking that a number is equal to an array which will result in a syntax error as they are not the same data types (and if it could work, which it doesn't, then a number value is never going to be equal to an array value regardless of whether that number is a member of the array).
  2. C# does not support passing arrays to Oracle. You can pass an associative array but, for some unknown reason, it does not support non-associative arrays.
  3. In Oracle, associative arrays are a PL/SQL data type which means that they can be used in a PL/SQL function, procedure or anonymous block but cannot be used in an SQL query.

What you would need to do is pass a PL/SQL associative array to some PL/SQL code that converts the associative array to a non-associative array and then use that in the query.

For examples of the Oracle side of passing PL/SQL associative arrays, see How to use associative array in "where in" clause? or pass integer array to oracle procedure by c#.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

But if you tried to execute for ex. from datagrip this query and for some reason it would workSELECT * FROM TESTTABLE WHERE ID IN ( : bind_variable_containing_collection_type ) so I cannot be agreed with the 1st point, what about the 2nd point it also support non-associate array for the ex. when you tried to use bulk update or create.
@semperfi From an Oracle point-of-view, a bind variable is always a single value and what I have said is correct. If you have an SQL driver that transforms your statement WHERE id IN (:array) to WHERE id IN (?, ?, ?, ?) before it transmits it to the database then Oracle will only see the statement it receives which contains multiple bind parameters (each of which is a scalar value) but that would be a function of your SQL driver (and since your question does not state any driver details we cannot comment on that).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.