I've asked the following on Oracle Community forum too, but SO is generally quicker to respond!
I found a problem with Managed ODP.Net v18.3.0 where having an Oracle Parameter in the SELECT statement will result in the DataTable having many columns marked as ReadOnly. Executing the same SELECT statement without using the Oracle Parameter, these columns will not be set as ReadOnly.
Here's an example C# console (.Net 4.7.1) app that demonstrates the problem:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Oracle.ManagedDataAccess.Client;
namespace ODP.NetParamsConsole
{
class SimpleScenario
{
internal static void ExecuteScenario()
{
DataSet ds1 = new DataSet();
string sqlNoParams = "SELECT F.FORMULA_CODE, nvl(null, 'Hard Coded Substitute Value') AS TEST_COLUMN, F.DESCRIPTION, F.YIELD, F.UOM_CODE FROM FSFORMULA F";
GetData(sqlNoParams, ds1, "TABLENOPARAMS", null);
string sqlWithParams = "SELECT F.FORMULA_CODE, nvl(null, :pSUBSTITUTE_VALUE) AS TEST_COLUMN, F.DESCRIPTION, F.YIELD, F.UOM_CODE FROM FSFORMULA F";
List<OracleParameter> selectParams = new List<OracleParameter>()
{
NewParameter("SUBSTITUTE_VALUE", "Parameter Substitute Value")
};
GetData(sqlWithParams, ds1, "TABLEWITHPARAMS", selectParams);
// This should return 0 different columns, but it will find that some of the columns are set to ReadOnly in the Param-based table.
List<string> diffResults = TableCompare(ds1, "TABLENOPARAMS", "TABLEWITHPARAMS");
Program.DisplayResults("Simple Scenario", diffResults);
}
private static void GetData(string SQL, DataSet DataSetToPopulate, string NewTableName, List<OracleParameter> OracleParameters)
{
using (OracleConnection conn = new OracleConnection("<my_connection_string>"))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
if (OracleParameters?.Count > 0)
{
foreach (OracleParameter p in OracleParameters)
cmd.Parameters.Add(p);
}
cmd.CommandText = SQL;
using (OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(cmd))
{
oracleDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
oracleDataAdapter.Fill(DataSetToPopulate, NewTableName);
}
}
}
}
private static OracleParameter NewParameter(string Name, object value)
{
OracleParameter p = new OracleParameter();
p.ParameterName = "p" + Name;
if (value != null)
p.Value = value;
return p;
}
private static List<string> TableCompare(DataSet dataSet, string table1, string table2)
{
List<string> diffColumns = new List<string>();
foreach (DataColumn dc1 in dataSet.Tables[table1].Columns)
{
if (dc1.ReadOnly != dataSet.Tables[table2].Columns[dc1.ColumnName].ReadOnly)
diffColumns.Add(dc1.ColumnName);
}
return diffColumns;
}
private static void DisplayResults(string TestName, List<string> DifferentColumns)
{
Console.WriteLine(string.Format("Scenario: {1} - There are {0:N0} different columns between the two tables.", DifferentColumns.Count, TestName));
foreach (string diffColumnName in DifferentColumns.OrderBy(dc => dc))
Console.WriteLine("Column: " + diffColumnName);
}
}
}
As you can see in the above, the SELECT statements are using the NVL function (can be any function), and one of the arguments is being supplied either by hard-coding the value into the string, or by passing an Oracle Parameter object. ODP.Net DataAdapter.Fill method is then used to fill a dataset with the results. If you use the MissingSchemaAction.AddWithKey option, then columns in the table filled by the SELECT that has a Parameter, will be set to ReadOnly. Compare that to the table that didn't have the Parameter, and none of the columns are set to ReadOnly.
I found that you can use Parameter elsewhere in the SELECT statement (like the WHERE clause), and this ReadOnly issue isn't present. I also found that you have to use a Function with a Parameter in the SELECT statement to cause the issue. Having the Parameter used by itself in the list of selected columns also doesn't present the problem. And as mentioned earlier, you have to have the MissingSchemaAction.AddWithKey option selected too.
