the generated method which calls the proc and fills the dataset is:
/// <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)
{
OracleCommand command = new OracleCommand(CreateCorrectStoredProcedureName(storedProcedureToCall));
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[i]);
}
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[i].OracleDbType == OracleDbType.RefCursor)
{
DataTable tableToFill = dataSetToFill.Tables.Add(parameters[i].ParameterName);
adapter.Fill(tableToFill, (OracleRefCursor)parameters[i].Value);
}
}
}
}
finally
{
command.Dispose();
// clean up a dangling automaticly opened connection if needed.
if(!(base.KeepConnectionOpen || base.IsTransactionInProgress))
{
base.CloseConnection();
}
}
return true;
}
(DataAccessAdapter)
the methods which call this piece of code are the methods in the RetrievelProcedures class in the generated code. If you look at these routines, you'll see that a Dispose() call is issued on the cursor parameters. So I don't know what more to do, as the fetching itself (the cursor consumption) is done inside the OracleDataAdapter object.
I'm not sure if your template version contains the code above and the dispose calls. If not, please let us know. Also it would be great if you'd mention which oracle provider you're using (MS Oracle or ODP.NET), which version (9i, 10g etc.) and if you've checked with oracle if there's a patch for your odp.net version (if you're using ODP.NET)