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.
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.
//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)
The truncated oracle procedure:
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;
Any ideas? I'm leaning towards improper reader use, but can't figure out what I'm missing. Thanks.