Help!! Why Am I Getting No Rows Back From Oracle Stored Procedure In C#? — oracle-tech

    Forum Stats

  • 3,715,654 Users
  • 2,242,821 Discussions
  • 7,845,480 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Help!! Why Am I Getting No Rows Back From Oracle Stored Procedure In C#?

5398727c-7080-4f7c-98c4-5b5e43f21e30
edited June 2019 in ODP.NET

Ok...so I am new to this forum and to Oracle in general.  I have created a few different stored procedures in Oracle for an application I am creating.  Typically we use SQL Server for DBs, but this data pre-exists in Oracle and it won't be moved.  The procedures were reviewed by someone at my company who does a lot of Oracle work, but with Java.  I have one procedure that has a SELECT from a single table, doesn't have any input or output parameters, and just returns the data using a refCursor.  This procedure works both in Oracle SQL Developer AND within the code of the application when I run it.  I have a second procedure that has a SELECT from multiple joined tables, 2 input and no output parameters, and returns the data exactly as the other one...using a refCursor.  This procedure however DOES work in Oracle SQL Developer, but does NOT work within the code of the application.  It always returns zero rows even though there are rows to return that ARE returned in Oracle SQL Developer when I execute the procedure. 

I have tried coding using a OracleDataReader to create a reader and an OracleDataAdapter to fill a DataTable with no change in what is returned.  I have tried putting the Query in a string in code and using command type of text instead of stored procedure as a test with no change.  My initial code to call the procedure with parameters is exactly the same as the code to call the one without except for adding the input parameters to the command object.  The code for procedure without parameters works and returns data every time.  The other always returns the table "structure" so I can view the field count, field names, etc...just no data.  Rows is always 0 in the data table.

using (OracleCommand cmd = new OracleCommand())

{

    // Set the command connection and define the stored procedure name

    cmd.Connection = conn;

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.CommandText = "DataStore.ProcedureName";

    // Set up the input parameters

    cmd.Parameters.Add("Param1", OracleDbType.Int32, param1, ParameterDirection.Input);

    cmd.Parameters.Add("Param2", OracleDbType.Int32, param2, ParameterDirection.Input);

    // Instantiate a data adapter and get the data into a table

    using (OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd))

    {

        dataAdapter.Fill(dt);

    }

}

Does anyone have any suggestions on what to look at?  I have been banging my head on this for days and am getting nowhere.

Answers

Sign In or Register to comment.