Hi,
I am new to use ODP.net. I came across the code which clones the oracle parameter before populating the dataset. The code says it is done because of ODP.net bug. I am not sure why it is done like. What is this bug? Do we really need that.
Please see the code below.
Thanks.
/// <summary>
/// Get a data table from Oracle using a SP that returns multiple rows not using a cursor
/// </summary>
/// <param name="storedProcedureName">Name of SP</param>
/// <param name="cursorName">Name of cursor</param>
/// <param name="parameters">(optional) list of OracleParameters (INPUT ONLY)</param>
/// <param name="connectionValue">Connection String (no TNSNAMES.ORA)</param>
/// <returns>DataTable or null</returns>
/// <exception cref="ArgumentNullException">If arguments are null</exception>
[SuppressMessage("Microsoft.Reliability", "CA2002")]
public static DataTable GetOracleDataTableFromStoredProcedureWithCursor(string storedProcedureName, string cursorName, List<OracleParameter> parameters, string connectionValue) {
System.Data.DataTable dt = null;
if (string.IsNullOrEmpty(storedProcedureName)) throw new ArgumentNullException("storedProcedureName");
if (string.IsNullOrEmpty(connectionValue)) throw new ArgumentNullException("connectionValue");
lock (typeof(OdpNetHelperLocker)) {
using (OracleConnection con = new OracleConnection()) {
try {
con.ConnectionString = connectionValue;
con.Open();
using (OracleCommand command = new OracleCommand()) {
command.Connection = con;
command.CommandText = storedProcedureName;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Clear(); // ODP.NET Bug
if ((parameters != null) && (parameters.Count > 0)) {
foreach (OracleParameter p in parameters) {
OracleParameter newP = (OracleParameter)p.Clone(); // ODP.NET Bug
if (!command.Parameters.Contains(newP)) {
command.Parameters.Add(newP);
}
}
}
OracleParameter po = MakeOutParameter(cursorName, OracleDbType.RefCursor, 16);
if (!command.Parameters.Contains(po)) command.Parameters.Add(po);
WriteLog(command);
OracleDataAdapter da = new OracleDataAdapter(command);
DataSet ds = new DataSet("ResultsDataSet");
da.Fill(ds, "Results");
if ((ds != null) && (ds.Tables != null) && (ds.Tables.Count > 0)) dt = ds.Tables[0];
}
} finally {
if (con != null) con.Close();
}
}
}
return dt;
}