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

Question

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.

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.

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.