RE: WHERE IN() with parameters

microsoft.public.dotnet.framework.adonet

24 Nov 2004 3:27 PM

Nick V.

It is possible to do this with oracle and a stored procedure; I'm doing it
currently with Oracle9i and the ODP.NET driver. I haven't checked if this is
possible with SQL Server or other versions of Oracle.

I'm not saying this is the best or only way to use an array in a query;
however, it is ONE of the ways. By all means, try everyone else's suggestions
first before tackling this confusing bit of code. Also, I suggest you find
someone familiar with Oracle who can answer your questions about the code
below. Use the code as a basis for your own code, cut n' paste may not work
without extensive modification.

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

//I like to start with an ArrayList because it's easy to add new items.
ArrayList arrLstItems = new ArrayList();
for (int i = 0; i< 10; i++)
{
    arrItems.Add(i);
}
string[] arrItems = (string[])arrLstItems.ToArray(typeof(System.String));

oraConn.ConnectionString = "YourOracleConnectionString";
oraConn.Open();
OracleCommand cmd = new OracleCommand("OraPackage.proc_YourProcedure",
oraConn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("p_ItemIn", OracleDbType.Varchar2, 50,
ParameterDirection.Input);
cmd.Parameters.Add("p_refcur", OracleDbType.RefCursor, DBNull.Value,
ParameterDirection.Output);

cmd.Parameters["p_ItemIn"].CollectionType =
OracleCollectionType.PLSQLAssociativeArray;
cmd.Parameters["p_ItemIn"].Value = arrItems;
cmd.Parameters["p_ItemIn"].ArrayBindStatus = new
OracleParameterStatus[arrItems.Length];
cmd.Parameters["p_ItemIn"].Size = arrItems.Length;

cmd.ExecuteNonQuery();

OracleDataAdapter da = new OracleDataAdapter(cmd);
DataTable dt = new DataTable("Items");
dt.BeginLoadData();
da.Fill(dt);
dt.EndLoadData();
oraConn.Close();
//You now have a usable DataTable.

Before the code will work, you'll have to setup oracle to use it.
You'll need an oracle type, package body with the plsqlarray, the procedure
you're calling, and a function to convert the plsqlarray to an array that
oracle likes.

In oracle, you'll need to create an Oracle Type.
This is necessary because oracle actually supports several different kinds
of arrays. It can accept a parameter of plsqlarray, but can select from an
oracletype (nested table).
the command to create the oracle type is:
TYPE "TBLOFVARCHAR50" AS TABLE OF VARCHAR2(50)

Your oracle package would look like this (I've edited my working code from
JDeveloper):
PACKAGE "OraPackage" AS
    --easy to manage refcursor
    TYPE REFCUR IS REF CURSOR;

    --PLSQLARRAY, how oracle makes arrays possible. (not easy, just possible)
    TYPE ASARRAY IS TABLE OF VARCHAR2(50) index by BINARY_INTEGER;

    procedure proc_YourProcedure
    (
        p_ItemIn in ASARRAY,
        p_refcur OUT refcur
    );

    function VarcharArr2tbl(p_arr IN ASARRAY) return tblOfVarchar50;
END;

PACKAGE BODY "OraPackage" AS
    procedure proc_YourProcedure
    (
        p_ItemIn in ASARRAY,
        p_refcur OUT refcur
    )
    AS
        v_tmparray tblofvarchar50;
    BEGIN
        --Oracle won't let a package select from a plsqlarray, it has to be an
Oracle Type.
        v_tmparray := varcharArr2tbl(p_ItemIn);

        --here's the best part. selecting from IN an array!
        OPEN p_refcur FOR
            SELECT title, txt
            FROM tblFoo
            WHERE
            bar IN (select cast(column_value as VARCHAR2(50)) from
TABLE(CAST(v_tmparray AS tblofvarchar50)));
    end proc_YourProcedure;

    --converts the plsqlarray to an oracle type
    function VarcharArr2tbl(p_arr IN ASARRAY) return tblOfVarchar50
    as
        l_data    tblOfVarchar50 := tblOfVarchar50();
    begin
        FOR i IN 1..p_arr.LAST
        LOOP
            l_data.EXTEND;
            l_data( l_data.count ) := p_arr(i);
        END LOOP;
        return l_data;
    end;
END;