Using an Oracle Data Reader with a ref cursor

Question

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:

.Parameters cannot be used like a method
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.

klaasjan69 · Accepted Answer · 2016-01-07 16:25:32Z

5

myReader = ((OracleRefCursor)myCMD.Parameters["p_cursor"].Value)).GetDataReader();

from https://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleRefCursorClass.htm

edited Jan 7, 2016 at 16:25

answered Jan 7, 2016 at 15:55

klaasjan69

1912 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SLaks · Accepted Answer · 2013-12-11 15:07:47Z

1

Unlike VB, C# uses brackets to index collections.

Use [], not ().

answered Dec 11, 2013 at 15:07

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

Comments

Soner Gönül · Accepted Answer · 2013-12-11 15:29:42Z

0

You should use [] instead of ().

From Arrays Tutorial

When declaring an array, the square brackets ([]) must come after the type, not the identifier. Placing the brackets after the identifier is not legal syntax in C#.

Also OracleCommand.Parameters property returns OracleParameterCollection not OracleDataReader

edited Dec 11, 2013 at 15:29

answered Dec 11, 2013 at 15:11

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

2 Comments

user2674605 Over a year ago

Thanks a lot for the help, do you know how I could get the data to be displayed? And how I can rectify the problem with the line: myReader = myCMD.Parameters("p_cursor");

Soner Gönül Over a year ago

@user2674605 Take a look at SqlDataReader Methods from MSDN. There a lot of methods that returns values from specific column and their types..

Alejandro Gallo SGM · Accepted Answer · 2018-04-05 21:02:04Z

-2

You have to use ExecuteReader()...

//(Your code...)
  OracleDataReader myReader = default(OracleDataReader);
  try
  {   //Here the magic
      myReader = myCMD.ExecuteReader();
  }
  catch (Exception myex)
  {
      Label1.Text = " " + myex.Message;
  }

 int x = 0;
 int count = 0;

 string actualValue = string.Empty;
 /*This data type is from .Net. Like: "System.DateTime" - "System.String" - etc*/
 string actualDataType = string.Empty; 

 while(myReader.Read())
 {
  //Here you can use GetValue and GetType
     actualValue = myReader.GetValue(count).ToString();
     actualDataType = myReader.GetType(count).ToString();
     count++;
 }

answered Apr 5, 2018 at 21:02

Alejandro Gallo SGM

1

1 Comment

cdonner Over a year ago

This is not how you read from a refCursor.

Collectives™ on Stack Overflow

Using an Oracle Data Reader with a ref cursor

4 Answers 4

Comments

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related