title: Returning multiple ref cursors from Oracle Procedure using DAAB & C#
body: <p>I want to return data from an Oracle procedure to populate some Label controls.  The procedure accepts 26 input parameters (search variable) and returns 3 output cursors.
  I have been successful returning data from a procedure which returns a single ref cursor using OracleCommand, a DataAdapter, and a DataSet, but have been having all kinds of issues returning data from a procedure returning multiple ref cursors.</p>
  
  <p>I was trying to use DAAB from the Enterprise Library, but had no success (original post).
  I now have gone back to an OracleCommand based off posts from Oracle and other dev sites.  I am still returning zero rows.  I can return the column names from my readers using "rdr_p_cursor_detail.GetName(0);", but When I try to return a value I get a "Operation is not valid due to the current state of the object." error.  </p>
  
  <pre><code>        //Open the connection to Oracle.
          OracleConnection cn = new OracleConnection(ConfigurationHelper.ConnectionStringSlabProcedures);
          OracleCommand cmd = new OracleCommand();
          cn.Open();
          cmd.Connection = cn;
  
  
          try
          {
  
              //Set the command text (SQL) of the Oracle command to the stored procedure "getSlab", which returns slab details.
              cmd.CommandText = ConfigurationHelper.ProcuedurePackageSchema + "." +
                                ConfigurationHelper.ProcuedurePackageName + "." +
                                "getSlab";
              //...and specify the command as a stored procedure.
              cmd.CommandType = CommandType.StoredProcedure;
  
  
              //Add the input and output parameters.  
              cmd.Parameters.Add("rb_category", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("rb_category_value", OracleDbType.Varchar2).Value = slabID;
              cmd.Parameters.Add("rb_type ", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("rb_chem_value", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("cb_inside_only", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("cb_appl_pending", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("cb_appl_soft_approved", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("cb_appl_hard_approved", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("cb_appl_mechanically", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("cb_as_cast", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("cb_as_dispoed", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("cb_chem_match", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("cb_near_match", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("txt_width_min", OracleDbType.Int32).Value = 0;
              cmd.Parameters.Add("txt_width_max", OracleDbType.Int32).Value = 0;
              cmd.Parameters.Add("txt_quality_min", OracleDbType.Int32).Value = 0;
              cmd.Parameters.Add("txt_quality_max", OracleDbType.Int32).Value = 0;
              cmd.Parameters.Add("txt_days_old_min", OracleDbType.Int32).Value = 0;
              cmd.Parameters.Add("txt_days_old_max", OracleDbType.Int32).Value = 0;
              cmd.Parameters.Add("txt_days_rev_min", OracleDbType.Int32).Value = 0;
              cmd.Parameters.Add("txt_days_rev_max", OracleDbType.Int32).Value = 0;
              cmd.Parameters.Add("txt_reviewer", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("cb_excl_hr", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("cb_excl_cr", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("cb_excl_galv", OracleDbType.Varchar2).Value = string.Empty;
              cmd.Parameters.Add("cb_excl_tin", OracleDbType.Varchar2).Value = string.Empty;
  
  
  
              //Add the ref-cursors as output parameters
              OracleParameter p_cursor_detail = cmd.Parameters.Add("p_cursor_detail", OracleDbType.RefCursor);
              p_cursor_detail.Direction = ParameterDirection.Output;
  
              OracleParameter p_cursor_comments = cmd.Parameters.Add("p_cursor_comments", OracleDbType.RefCursor);
              p_cursor_comments.Direction = ParameterDirection.Output;
  
              OracleParameter p_cursor_grades = cmd.Parameters.Add("p_cursor_grades", OracleDbType.RefCursor);
              p_cursor_grades.Direction = ParameterDirection.Output;
  
              // Execute the command
              cmd.ExecuteNonQuery();
  
  
              // Construct an OracleDataReader from the REF CURSOR
              OracleDataReader rdr_p_cursor_detail = ((OracleRefCursor)p_cursor_detail.Value).GetDataReader();
  
              if (rdr_p_cursor_detail.Read())
              {
  
                  //This would return an error if it was outside this if statement.
                  //"Operation is not valid due to the current state of the object."
                  OracleString oracleString1 = rdr_p_cursor_detail.GetOracleString(1);
                  LabelSlabID.Text = oracleString1.ToString();
  
                  //This correctly returns column name/header.
                  //LabelSlabID.Text = rdr_p_cursor_detail.GetName(0);
  
              }
              else
              {
                  LabelSlabID.Text = "No Data";
              }
  
  
  
              rdr_p_cursor_detail.Close();
              rdr_p_cursor_detail.Dispose();
  
              p_cursor_detail.Dispose();
              p_cursor_comments.Dispose();
              p_cursor_grades.Dispose();
  
          }
          catch (Exception e)
  </code></pre>
  
  <p>The truncated oracle procedure:</p>
  
  <pre><code>PROCEDURE getSlab         
  (rb_category                  IN VARCHAR2
  ,rb_category_value        IN VARCHAR2
  ,rb_type                      IN VARCHAR2
  ,rb_chem_value            IN VARCHAR2                                
  ,cb_inside_only           IN VARCHAR2     
  ,cb_appl_pending          IN VARCHAR2   
  ,cb_appl_soft_approved    IN VARCHAR2   
  ,cb_appl_hard_approved    IN VARCHAR2             
  ,cb_appl_mechanically     IN VARCHAR2   
  ,cb_as_cast                   IN VARCHAR2   
  ,cb_as_dispoed            IN VARCHAR2   
  ,cb_chem_match            IN VARCHAR2   
  ,cb_near_match            IN VARCHAR2   
  ,txt_width_min            IN NUMBER
  ,txt_width_max            IN NUMBER
  ,txt_quality_min          IN NUMBER
  ,txt_quality_max          IN NUMBER
  ,txt_days_old_min         IN NUMBER
  ,txt_days_old_max         IN NUMBER     
  ,txt_days_rev_min         IN NUMBER
  ,txt_days_rev_max         IN NUMBER
  ,txt_reviewer             IN VARCHAR2         
  ,cb_excl_hr               IN VARCHAR2
  ,cb_excl_cr               IN VARCHAR2
  ,cb_excl_galv             IN VARCHAR2
  ,cb_excl_tin                  IN VARCHAR2
  ,cur_OUT OUT t_cursor1 
  ,cur_OUT1 OUT t_cursor2
  ,cur_OUT2 OUT t_cursor3) IS
  
  
   --All the queries and so forth are performed and data sent to temp tables.
    OPEN v_cursor1 FOR
          SELECT * FROM TABLE(CAST(g_slab_table AS getSlab_table));
      cur_OUT := v_cursor1;   
  
    OPEN v_cursor2 FOR
           SELECT * FROM TABLE(CAST(g_comment_table AS getComment_table));
      cur_OUT1 := v_cursor2;      
  
    OPEN v_cursor3 FOR
           SELECT * FROM TABLE(CAST(g_grades_table AS getGrades_table));
      cur_OUT2 := v_cursor2;      
  </code></pre>
  
  <p>Any ideas?  I'm leaning towards improper reader use, but can't figure out what I'm missing.
  Thanks.</p>