1st attempt at Oracle ref cursor, help please

[Jason Shohet]

Below is my first Oracle ref cursor proc call from a code-behind page. The
proc is located in a package, and the package specification defines the ref
cursor. The proc takes 1 param in (Oracle 'number' type). And returns ref
cursor. Unfort. it fails on ExecuteReader( ), and I get this rediculous
error:
"Invalid operation. The connection is closed".

I hobbled this code from various postings, so I'm not sure if that
ExecuteReader( ) line is correct with the CommandBehavior.CloseConnection
code in there. Can any one comment ? TY !!! -- Jason Shohet

private void btnRetrieve_clicked(object sender, System.EventArgs e)
{

DataSet ds = new DataSet();

OracleConnection con = new OracleConnection("Data Source=WPRP; User
ID=wc9; password=wc91abc;");

OracleCommand cmd = new OracleCommand("", con);

cmd.CommandType = CommandType.StoredProcedure ;

cmd.CommandText ="pkg_get_bldgs.PCOC_PREMISYS_BLDG_CNTCT";

object my_DBNull = Convert.DBNull;

cmd.Parameters.Add(new OracleParameter("DOF_NM_ADDR_REFCUR",
OracleType.Cursor, 2000, ParameterDirection.Output, true, 0,0, "",
DataRowVersion.Default, my_DBNull));

cmd.Parameters.Add(new OracleParameter("AN_BLDG_ID",
OracleType.Number, 0, ParameterDirection.Input, true, 0, 0, "",
DataRowVersion.Default, (int) 693));

OracleDataReader dr =
cmd.ExecuteReader(CommandBehavior.CloseConnection);

dg1.DataSource = dr;

dg1.DataBind();

}

 

[David Browne]

Below is my first Oracle ref cursor proc call from a code-behind page. The
proc is located in a package, and the package specification defines the ref
cursor. The proc takes 1 param in (Oracle 'number' type). And returns ref
cursor. Unfort. it fails on ExecuteReader( ), and I get this rediculous
error:
"Invalid operation. The connection is closed".

I hobbled this code from various postings, so I'm not sure if that
ExecuteReader( ) line is correct with the CommandBehavior.CloseConnection
code in there. Can any one comment ? TY !!! -- Jason Shohet

Try this:


private void btnRetrieve_clicked(object sender, System.EventArgs e)
{

using (OracleConnection con = new OracleConnection("Data Source=WPRP;
User ID=wc9; password=wc91abc;")
{
con.Open();
OracleCommand cmd = new
OracleCommand("pkg_get_bldgs.PCOC_PREMISYS_BLDG_CNTCT", con);
cmd.CommandType = CommandType.StoredProcedure ;

cmd.Parameters.Add(new OracleParameter("AN_BLDG_ID",
OracleType.Number, 0, ParameterDirection.Input, true, 0, 0, "",
DataRowVersion.Default, (int) 693));

cmd.Parameters.Add(new OracleParameter("DOF_NM_ADDR_REFCUR",
OracleType.Cursor, 2000, ParameterDirection.Output, true, 0,0, "",
DataRowVersion.Default, DbNull.Value));

using (OracleDataReader dr = cmd.ExecuteReader())
{
dg1.DataSource = dr;
dg1.DataBind();
}
}
}


David

 

[Jason Shohet]

David thanks.
I got it working thanks to you. The one thing that didn't work was the
DbNull.Value param below. I had to first do:
object. myDBNull = Convert.DBNull; and then pass myDBNull as the
argument.

I'm not sure why, but thats the only way it compiled

THanks!
Jason Shohet