Sometimes you want to select rows where a value is in a specific collection. Here’s an example that show how you can select all the rows in the TEST table with an id of 1, 2 or 3. First we create an SQL type to contain a list of numbers:
CREATE TYPE LIST_NUMBER AS TABLE OF NUMBER(10); /
Next thing to do is add a custom type and function header to the package specification:
PACKAGE TIMVW.TESTPACKAGE AS TYPE CRSR_REF IS REF CURSOR; TYPE ARR_IDS IS TABLE OF TEST.TEST_ID%Type INDEX BY BINARY_INTEGER; PROCEDURE GET_TESTS ( P_IDS IN ARR_IDS, P_CURSOR OUT CRSR_REF ); END;
And offcourse we have to implement the function in the body:
PROCEDURE GET_TESTS
(
P_IDS IN ARR_IDS,
P_CURSOR OUT CRSR_REF
)
AS
V_IDS LIST_NUMBER := LIST_NUMBER();
BEGIN
V_IDS.EXTEND(P_IDS.COUNT);
FOR i IN P_IDS.FIRST .. P_IDS.LAST LOOP
V_IDS(i) := P_IDS(i);
END LOOP;
OPEN P_CURSOR FOR
SELECT
TEST.TEST_ID,
TEST.NAME,
TEST.TYPE_CODE
FROM
TEST
WHERE
TEST_ID IN (SELECT * FROM TABLE(V_IDS))
ORDER BY
TEST.TEST_ID ASC;
END;
Now that we have done all this we can consume the function from our client code:
using (OracleConnection conn = new OracleConnection("User Id=u;password=p;Data Source=ORCL"))
{
conn.Open();
OracleCommand command = conn.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "TIMVW.TESTPACKAGE.GET_TESTS";
command.Parameters.Add("P_IDS", OracleDbType.Int32, new int[] { 1, 2, 3 }, ParameterDirection.Input);
command.Parameters["P_IDS"].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
command.Parameters.Add("P_CURSOR", OracleDbType.RefCursor, ParameterDirection.Output);
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("test id: " + reader.GetDecimal(0));
}
Console.Write("{0}Press any key to continue...", Environment.NewLine);
Console.ReadKey();
}
