We help IT Professionals succeed at work.

Call Oracle Stored Proc from C# that returns RefCursor

triplebd69
triplebd69 asked
on
5,196 Views
Last Modified: 2013-04-09
I am trying to call a SP in Oracle that returns RefCursor type to C#.

I have attached SP code.

I get the error when the OracleDataReader is called.


Here is the C# code I have to this point...
public static DataTable LoadDataTableSQL(string storedProcedureName, params OracleParameter[] arrParam)
        {
            try
            {
                DataTable dt = new DataTable();

                // Open the connection
                using (OracleConnection cnn = new OracleConnection(Functions.DBConnection))
                {
                    cnn.Open();

                    // Define the command
                    using (OracleCommand cmd = new OracleCommand())
                    {
                        cmd.Connection = cnn;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = storedProcedureName;
                        cmd.CommandTimeout = 0;

                        // Handle the parameters
                        if (arrParam != null)
                        {
                            foreach (OracleParameter param in arrParam)
                            {
                                cmd.Parameters.Add(param);
                            }
                        }

                        using (OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.Default))
                        {
Error.PNG
PL-SQL.sql
Comment
Watch Question

Obadiah ChristopherDeveloper User Interface
CERTIFIED EXPERT

Commented:
This is what the Oracle guideline says


When the ExecuteNonQuery method is invoked on a command that returns one or
more REF CURSOR data types, each of the OracleCommand parameters that are bound
as an OracleDbType.RefCursor gets a reference to an OracleRefCursor object.
To create an OracleDataReader object from an OracleRefCursor object, invoke
the GetDataReader method from the OracleRefCursor object. Subsequent calls to
the GetDataReader method return a reference to the same OracleDataReader
object.
To populate a DataSet with an OracleRefCursor object, the application can invoke
a Fill method of the OracleDataAdapter class that takes an OracleRefCursor
object. Similar to the OracleDataReader object, an OracleRefCursor object is
forward-only. Therefore, once a row is read from an OracleRefCursor object, that
same row cannot be obtained again from it unless it is populated again from a query.
When multiple REF CURSOR data types are returned from a command execution as
OracleRefCursor objects, the application can choose to create an
OracleDataReader object or populate a DataSet with a particular
OracleRefCursor object. All the OracleDataReader objects or DataSet objects
created from the OracleRefCursor objects are active at the same time, and can be
accessed in any order.

Author

Commented:
Very new to Oracle can I get a code example?
Developer User Interface
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I must be missing something, because I don't have a OracleDBType only OracleType.

Nor RefCursor only Cursor.
Obadiah ChristopherDeveloper User Interface
CERTIFIED EXPERT

Commented:
Looks like you are not using ODP.Net. That is the recommended provider for accessing Oracle database from .Net

Author

Commented:
Would prefer not to.  Can't I use the System.Data.OracleDataClient?
Robert SchuttSoftware Engineer
CERTIFIED EXPERT

Commented:
Try this:
OracleParameter op = new OracleParameter();
op.ParameterName = "ritems";
op.OracleType = OracleType.Cursor;
op.Direction = ParameterDirection.Output;
// just an example
dataGridView1.DataSource = LoadDataTableSQL("SP_GET_CUSTOMER", new OracleParameter[] { op });

Open in new window

In your function, to return the datatable instead of reading it:
using (OracleDataAdapter da = new OracleDataAdapter(cmd)) {
    da.Fill(dt);
    return dt;
}

Open in new window

Obadiah ChristopherDeveloper User Interface
CERTIFIED EXPERT

Commented:
robert_schutt's code would work.

Just to make a point about the below article that points that OracleDataClient is depreciated

http://msdn.microsoft.com/en-us/library/system.data.oracleclient.aspx

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.