Hello,
I am stumped. Hopefully another pair of eyes will see what mine cannot. I am
calling a stored procedure that accepts a single output parameter [the proc
has been dumbed down to limit variables of my problem] that is a ref cursor.
When I make the call I get now data. I have used both adapter and
datareaders. If I call the proc from another proc I can loop through the
results. I am including the code of my c# function and the stored proc.
The version number of my Oracle.DataAccess.dll is 10.1.0.303.
** Stored Proc ***********************
TYPE refcursor is ref cursor ;
procedure P_CUSTOMER_INFO_GET_SLIM( thecur out refcursor )
is
begin
open thecur for
select *
from customer_info
where rownum < 21;
end P_CUSTOMER_INFO_GET_SLIM;
** C# Func ***************************
private DataSet _FetchCustomerData2(string CustNbr, string ShipToSuffix)
{
string sc = "PKG_CUSTOMER_CONTACT.P_CUSTOMER_INFO_GET_SLIM";
OracleConnection con = new
OracleConnection(_Database.GetConnection().ConnectionString);
OracleCommand cmd = new OracleCommand(sc, con);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
// must pass strings with data
if (CustNbr.Length==0 || ShipToSuffix.Length==0)
{
return null;
}
try
{
// we are calling a stored proc so set it
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("thecur", OracleDbType.RefCursor, DBNull.Value,
ParameterDirection.Output);
da.Fill(ds, "Customer"); // no data ?????????????????
// Close and Dispose OracleConnection object
con.Close();
con.Dispose();
return ds;
...