First of all, my goal is to return an array that I've defined back to C#.
Here is the type that I've defined:
create or replace type EMPARRAY is VARRAY(20) of VARCHAR2(30)
here is the function I'm trying to call
create or replace function getEmpArray
Return EMPARRAY as
V_data EMPARRAY := EMPARRAY();
Cursor student_test is
SELECT student_id
FROM students;
Begin
For stu_rec In student_test
Loop
V_data.extend;
V_data(V_data.count) := stu_rec.student_id;
End loop;
Return V_data;
END;
Now, I've installed the ODP.NET connector from oracle and am attempting to get that array returned but I'm having issues. Here is my c# code:
public void test_Click(Object obj, EventArgs ea)
{
//create connection string
String strConnection = "data source=foo;user id=foo;password=foo;";
using (OracleConnection conn = new OracleConnection(strConnection))
{
OracleCommand objCmd = new OracleCommand();
objCmd.Connection = conn;
objCmd.CommandText = "getEmpArray";
objCmd.CommandType = CommandType.StoredProcedure;
OracleParameter returnValue = new OracleParameter("retVal", OracleCollectionType.PLSQLAssociativeArray);
returnValue.Size = 100;
returnValue.Direction = ParameterDirection.ReturnValue;
objCmd.Parameters.Add(returnValue);
try
{
conn.Open();
Console.WriteLine("Connection Successful");
}
catch (OracleException e)
{
MessageBox.Show(e.Message, "Oracle Exception");
}
}//end using
}//end test Click
What I have compiles fine but when I fire the click event, it gives me an error saying that Object reference not set to an instance of an object. It points to this line:
OracleParameter returnValue = new OracleParameter("retVal", OracleCollectionType.PLSQLAssociativeArray);
So any suggestions on how I should go about doing this? I'm probably completely off base here so any help would be appreciated.