C # / ODP.NET: Big IN Workaround

Question

We have a C # component that processes lists of elements of arbitrary size in IN clauses for semi-arbitrary SQL queries SELECT. Essentially it comes down to getting something like:

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

... where "..." is the only part of the request that the component is allowed to modify.

Currently, the component will insert a set of named binding parameters, separated by commas, and then attach the corresponding IDbDataParameter objects to the command and execute; the component receives information about the types of parameters that it should bind. This works well until the calling code sets the parameter set more than the database is ready to accept. The goal here is to get such large suites that work with queries against Oracle 11gR2 through ODP.NET.

This task is somewhat complicated by the following approaches, which are considered unacceptable for those who establish requirements:

• Global temporary tables
• Saved Procedures
• All requiring execution CREATE TYPE

A solution to this is not required to fulfill just one request.

, , , :

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;

//...

, , COUNT, ( , SQLDeveloper SELECT, ). ODP.NET .

:

• , ?
• , ?

( , ( ) , .)

Answer 1

, , ? , :

OracleCommand :

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

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

ArrayBindCount , .

{inListTableName} Guid.NewGuid().ToString().

{dbDataType} , .

OracleParameter OracleCommand "inValue" , , . Hashset ( , ), .ToArray() .

. .

sql in select sql statement: (SELECT {inListTableName}.inValue FROM {inListTableName})

:

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

, .

, :

DROP TABLE {inListTableName};

. .

, / inListTable, .

:

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

TransmitValueList , prep.

GetInListSQLSnippet (SELECT {inListTableName}.inValue FROM {inListTableName});

RemoveValueList .

db inListTableName.

, .

: , , NOT IN . , , :

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

NOT IN , . , , , 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

, , , , ?

. , ...

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

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

. , , , .

, parallelism, .