Issue with executing stored procedure with an OUT refcursor when using ODP.Net managed with Enterprise library

I am trying to execute multiple (3) stored procedures one by one using managed ODP.Net with Enterprise Library. Each stored procedure returns an OUT cursor, each of which has been named "cur_out". What is weird is that the first stored procedure always returns records, however subsequent called to ExecuteDataSet() for remaining two SP's never return any records. This works perfectly with System.Data.OracleClient so I know there is no issue with the stored procedures as such. I am not able to figure out why these SP's are not returning anything. Below is my sample code:

DbCommand cmd = DB.GetStoredProcCommand("EBS_EOM_PKG.SP1");
DB.AddInParameter(cmd, "PI_PARAM1", DbType.AnsiString);
DB.SetParameterValue(cmd, "PI_PARAM1", param1);
DB.AddInParameter(cmd, "PI_PARAM2", DbType.AnsiString);
DB.SetParameterValue(cmd, "PI_PARAM2", param2);
if (cmd is Oracle.ManagedDataAccess.Client.OracleCommand)

OracleParameter cur = new OracleParameter("cur_out",
OracleDbType.RefCursor, 0,
ParameterDirection.Output, true,
0, 0, String.Empty,
DataRowVersion.Current,
Convert.DBNull);
cmd.Parameters.Add(cur);

DataSet dsSub = DB.ExecuteDataSet(cmd); <--WORKS i.e. returns records

cmd = DB.GetStoredProcCommand("EBS_EOM_PKG.SP2");
DB.AddInParameter(cmd, "PI_PARAM1", DbType.AnsiString);
DB.SetParameterValue(cmd, "PI_PARAM1", param1);
DB.AddInParameter(cmd, "PI_PARAM2", DbType.AnsiString);
DB.SetParameterValue(cmd, "PI_PARAM2", param2);
if (cmd is Oracle.ManagedDataAccess.Client.OracleCommand)

OracleParameter cur = new OracleParameter("cur_out",
OracleDbType.RefCursor, 0,
ParameterDirection.Output, true,
0, 0, String.Empty,
DataRowVersion.Current,
Convert.DBNull);
cmd.Parameters.Add(cur);

DataSet dsSub1 = DB.ExecuteDataSet(cmd); <--FAILS i.e. no records returned
DataTable dt1 = dsSub1.Tables[0];
dsSub1.Tables.Remove(dt1);
dt1.TableName = "Ported DataTable 1";//Provide name
explicitly to prevent name conflicts with existing datatable
dsSub.Tables.Add(dt1);

cmd = DB.GetStoredProcCommand("EBS_EOM_PKG.SP3");
DB.AddInParameter(cmd, "PI_PARAM1", DbType.AnsiString);
DB.SetParameterValue(cmd, "PI_PARAM1", param1);
DB.AddInParameter(cmd, "PI_PARAM2", DbType.AnsiString);
DB.SetParameterValue(cmd, "PI_PARAM2", param2);
if (cmd is Oracle.ManagedDataAccess.Client.OracleCommand)

OracleParameter cur = new OracleParameter("cur_out",
OracleDbType.RefCursor, 0,
ParameterDirection.Output, true,
0, 0, String.Empty,
DataRowVersion.Current,
Convert.DBNull);
cmd.Parameters.Add(cur);

DataSet dsSub2 = DB.ExecuteDataSet(cmd); <--FAILS i.e. no records returned

DataTable dt2 = dsSub2.Tables[0];

dsSub2.Tables.Remove(dt2);
dt2.TableName = "Ported DataTable 2";//Provide name
explicitly to prevent name conflicts with existing datatable
dsSub.Tables.Add(dt2);


Can someone point out what am I missing here? Is there something I can do differently? Please note that I am using Oracle 12c DB which has issue returning multiple ref cursors in a single stored procedure, so have to execute these seperately and then combine the result in a single Dataset.

1 Answer
1

I recommend declaring a brand new DbCommand cmd2 and DbCommand cmd3 instead of trying to reuse the initial DbCommand cmd. In the past, I've had a lot of problems re-using variables related to Oracle parameters and connections, and a lot of problems tend to go away if you just use new variables for every aspect of the database connection.

DbCommand cmd2

DbCommand cmd3

DbCommand cmd

For your code specifically, it seems like you are continually adding the same two parameters to your cmd. But I don't think that is re-setting the value in the existing parameter to the new value, I think it is adding another, duplicate, parameter to the collection of parameters, and that causes problems.

cmd.

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy