I have developed an application that creates tables based on external definitions. If these definitions change, the application drops and recreates the tables affected. It has to do that while the connection is opened because that task is part of a group of tasks. However, if I query a table that has changed with "select *" afterwards, I get the columns of the old definition. If columns have been added to the table, "select *" does not see it. Here is a sample code that demonstrates this:
private static void OraSelectStarProblem()
{
var tabDef1 = "CREATE TABLE TEMP_PS_000 (SESSIONID RAW(16), EXECNR NUMBER, DUEDATE DATE)";
//var tabDef2 = "CREATE TABLE TEMP_PS_000 (YASESSIONID RAW(16), YAEXECNR NUMBER, YADUEDATE DATE, NEWFIELD VARCHAR2(100))";
var tabDef2 = "CREATE TABLE TEMP_PS_000 (YADUEDATE DATE, NEWFIELD VARCHAR2(100), SOMEOTHER integer)"; // causes ORA-00932 when querying
var selectCmd = "select * from TEMP_PS_000";
using (var conn = new OracleConnection(GetConnStr()))
{
conn.Open();
//PerformSelectStar(selectCmd, conn);
DropTempTable(conn);
Console.Write("Creating TEMP_PS_000 with 3 columns... ");
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = tabDef1;
cmd.ExecuteNonQuery();
}
Console.WriteLine("Done!");
PerformSelectStar(selectCmd, conn);
//conn.PurgeStatementCache(); // no effect
//conn.FlushCache(); // no effect
/*Console.WriteLine("Resetting connection");
conn.Close();
OracleConnection.ClearPool (conn);
conn.Open();*/
DropTempTable(conn);
//conn.PurgeStatementCache(); // no effect
//conn.FlushCache(); // no effect
Console.Write("Creating TEMP_PS_000 with 4 columns... ");
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = tabDef2;
cmd.ExecuteNonQuery();
}
Console.WriteLine("Done!");
PerformSelectStar(selectCmd, conn);
}
}
private static void DropTempTable(OracleConnection conn)
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "begin drop_table('TEMP_PS_000'); end;";
cmd.ExecuteNonQuery();
}
}
private static void PerformSelectStar(string selectCmd, OracleConnection conn)
{
try
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = selectCmd;
var dataAdapter = new OracleDataAdapter(cmd);
var dataSet = new DataSet();
dataAdapter.Fill(dataSet);
var columns = "";
foreach (DataColumn column in dataSet.Tables[0].Columns)
{
columns += column.ColumnName + " ";
}
Console.WriteLine("Columns: " + columns);
}
}
catch (Exception ex)
{
Console.WriteLine("PerformSelectStar: " + ex.Message);
}
}
Software versions used: .net 4.5.1 and Oracle 12.1
If the part that resets the connection is activated, "select *" works, but then all data in temporary tables is flushed, which is not desired.
I would like to know if there is some method of telling either ODP.net or Oracle - whichever is the reason for this strange behaviour - that a table's definition has been changed and that it should reread the table definition prior to executing "select *".
