C#/ODP.NET: large IN clause workaround

Question

We have a C# component that handles attaching arbitrary-sized element lists into IN clauses for semi-arbitrary SQL SELECT queries. Essentially this boils down to receiving something like:

SELECT COUNT(*) FROM a WHERE b IN (...)

...where the "..." is the only portion of the query the component is allowed to modify.

Currently the component will insert a comma-separated set of named bind parameters, then attach the corresponding IDbDataParameter objects to the command and execute; the component is made aware of the types for the parameters it has to bind. This works well, until the calling code supplies a parameter set larger than the database is willing to accept. The objective here is to get such large sets working with queries against Oracle 11gR2 via ODP.NET.

This task is complicated somewhat by the following approaches being deemed unacceptable by those setting the requirements:

• Global Temporary Tables
• Stored procedures
• Anything requiring CREATE TYPE to have been executed

The solution to this is not required to execute only one query.

I'm trying to make this work by binding the clause as an array, using code sourced from elsewhere:

IList<string> values;

//...

OracleParameter parameter = new OracleParameter();
parameter.ParameterName = "parm";
parameter.DbType = DbType.String;
parameter.Value = values.ToArray();
int[] sizes = new int[values.Count];
for (int index = 0; index < values.Count; index++)
{
    sizes[index] = values[index].Length;
}
parameter.ArrayBindSize = sizes;

//...

The command subsequently executes without throwing an exception, but the value returned for COUNT is zero (compared to the expected value, from running the query in SQLDeveloper with a nested SELECT returning the same parameter set). Going through the ODP.NET docs hasn't brought any joy thus far.

The questions for this are:

• Is there a way to make the above parameter attachment work as expected?
• Is there another viable way to achieve this without using one of the vetoed approaches?

(I'm aware this is similar to this (unanswered) question, but that scenario does not mention having the same restrictions on approaches.)

Answer 1

Well, since you are not allowed to use Global Temporary Tables, are you at least allowed to create normal tables? If so, here is a way:

Create an OracleCommand object with the following command text:

@"BEGIN
CREATE TABLE {inListTableName}
(
  inValue   {dbDataType}
)

INSERT INTO {inListTableName}(inValue) VALUES(:inValue);
END"

Set the ArrayBindCount on the command object to the number of items you need in your in list.

Replace {inListTableName} with the Guid.NewGuid().ToString().

Replace the {dbDataType} with the correct oracle data type for the list of values that you want to use in your in clause.

Add an OracleParameter to the OracleCommand named "inValue" and set the value of the parameter to an array containing the values that you want in your in clause. If you have a Hashset (which I recommend using to avoid sending unnecessary duplicates), use the .ToArray() on it to get an array.

Execute this command. This is your prep command.

Then use the following sql snippet as the value portion of the in clause in your select sql statement: (SELECT {inListTableName}.inValue FROM {inListTableName})

For example:

SELECT FirstName, LastName FROM Users WHERE UserId IN (SELECT {inListTableName}.inValue FROM {inListTableName});

Execute this command to get a reader.

Lastly, one more command with the following command text:

DROP TABLE {inListTableName};

This is your cleanup command. Execute this command.

You might want to create an alternate schema/user to create the inListTable so that you can grant appropriate permissions to your user to only create tables in that schema.

All of this can be encapsulated in a reusable class with the following interface:

public interface IInListOperation
{
    void    TransmitValueList(OracleConnection connection);
    string  GetInListSQLSnippet();
    void    RemoveValueList();
}

TransmitValueList would create your prep command, add the parameter and execute the prep command.

GetInListSQLSnippet would simply return (SELECT {inListTableName}.inValue FROM {inListTableName});

RemoveValueList cleans up.

The constructor for this class would take the value list and oracle db data type, and generate the inListTableName.

If you can use a Global Temporary Table, I would recommend that over creating and dropping tables.

Edit: I'd like to add that this approach works well if you have clauses involving NOT IN lists or other inequality operators. Take the following for example:

SELECT FirstName, LastName FROM Users WHERE Status == 'ACTIVE' OR UserID NOT IN (1,2,3,4,5,6,7,8,9,10);

If you use the approach of splitting the NOT IN part up, you will end up getting invalid results. The following example of dividing the previous example will return all users instead of all but those with UserIds 1-10.

SELECT FirstName, LastName FROM Users WHERE UserID NOT IN (1,2,3,4,5)
UNION
SELECT FirstName, LastName FROM Users WHERE UserID NOT IN (6,7,8,9,10);

Answer 2

Maybe this is too simplistic for the kind of query you're doing, but is there any reason why you couldn't split this into several queries and combine the results together in code?

i.e. Let's imagine 5 elements are too many for the query...

select COUNT(*) from A where B in (1,2,3,4,5)  

you'd separately perform

select COUNT(*) from A where B in (1,2,3)
select COUNT(*) from A where B in (4,5)

and then add those results together. Of course, you'd have to make sure that in-clause list is distinct so you don't double up on your counts.

If you can do it this way, there is an added opportunity for parallelism if you're allowed more than one connection.