Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Return pl/sql array to c#

707697Jul 7 2009 — edited Jul 8 2009
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 5 2009
Added on Jul 7 2009
1 comment
6,857 views