Tim Van Wassenhove home

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();
}