Ram said:
This is being run as the command type = stored procedure. The issue
seems to be in binding the refcursor back into ADO.NET
I can't see the original posting of the thread, so forgive me if this
info was already posted earlier, but in both ODP.NET and MS Oracle
provider you can bind output parameters which are of type REF CURSOR to
datatables using the OracleDataAdapter. The ODP.NET provider comes with
examples which show you how to do this. It's very easy, as everything
is done for you. Typical example: (ODP.NET)
/// <summary>
/// Calls the specified retrieval stored procedure in the Oracle
database. Fills the
/// specified DataSet. Will participate in the transaction if a
transaction is in progress.
/// </summary>
/// <param name="storedProcedureToCall">Stored procedure to call</param>
/// <param name="parameters">array of parameters to specify</param>
/// <param name="dataSetToFill">DataSet to fill by the stored
procedure</param>
/// <returns>true if succeeded, false otherwise</returns>
public virtual bool CallRetrievalStoredProcedure(string
storedProcedureToCall, OracleParameter[] parameters, DataSet
dataSetToFill)
{
DynamicQueryEngine dqe =
(DynamicQueryEngine)CreateDynamicQueryEngine();
string procName =
dqe.GetNewPerCallStoredProcedureName(storedProcedureToCall);
procName = DynamicQueryEngine.GetNewStoredProcedureName(procName);
OracleCommand command = new OracleCommand(procName);
command.Connection = (OracleConnection)base.GetActiveConnection();
if(base.IsTransactionInProgress)
{
((IDbCommand)command).Transaction =
(OracleTransaction)base.PhysicalTransaction;
}
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = base.CommandTimeOut;
for(int i=0;i<parameters.Length;i++)
{
command.Parameters.Add(parameters
);
}
try
{
base.OpenConnection();
command.ExecuteNonQuery();
// for each cursor parameter, create a datatable in dataset and fill
it.
using(OracleDataAdapter adapter =
(OracleDataAdapter)CreateNewPhysicalDataAdapter())
{
for (int i = 0; i < parameters.Length; i++)
{
if(parameters.OracleDbType == OracleDbType.RefCursor)
{
DataTable tableToFill =
dataSetToFill.Tables.Add(parameters.ParameterName);
adapter.Fill(tableToFill, (OracleRefCursor)parameters.Value);
}
}
}
}
finally
{
command.Dispose();
// clean up a dangling automaticly opened connection if needed.
if(!(base.KeepConnectionOpen || base.IsTransactionInProgress))
{
base.CloseConnection();
}
}
return true;
}
FB
--
My