Using an Oracle Data Reader with a ref cursor

Viewed 7658

I am trying to use an Oracle Data Reader with a ref cursor within a package I have created.

Using Visual Studio and C#.

Although I am getting two build errors concerning my reader:

  1. .Parameters cannot be used like a method
  2. MyReader is a variable but is used like a method.

Below is my code:

protected void Page_Load(object sender, EventArgs e)
        {
            string oradb = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=*****)(PORT=***))(CONNECT_DATA=(SERVICE_NAME=***)));User Id=APPS;Password=****;";
            OracleConnection conn = new OracleConnection(oradb);  // C#
            conn.Open();


            OracleCommand myCMD = new OracleCommand();
            myCMD.Connection = conn;
            myCMD.CommandText = "apps.cie_install_base_data.get_serial_trx_hist";
            myCMD.CommandType = CommandType.StoredProcedure;
            myCMD.Parameters.Add(new OracleParameter("p_cursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output;
            myCMD.Parameters.Add("p_serial_number", OracleDbType.Varchar2, 30).Value = "M5605946";
            OracleDataReader myReader = default(OracleDataReader);
            try
            {
                myCMD.ExecuteNonQuery();
            }
            catch (Exception myex)
            {
                Label1.Text = " " + myex.Message;
            }

            myReader = myCMD.Parameters("p_cursor"); 

            int x = 0;
            int count = 0;

            count = 0;

            //myReader = myCMD.ExecuteReader();
            while (myReader.Read()) {
            for (x = 0; x <= myReader.FieldCount - 1; x++) {
            Label3.Text = myReader(x) + " ";
            }
            Label4.Text = " ";
            count += 1;
            }


            Label5.Text = (count + " Rows Returned.");

            myReader.Close();
            conn.Close();
        }
    }
 }

If anyone can see why I am getting these build errors, or knows how I can get the data to be displayed properly that would be great.

4 Answers
Related