OracleDataAdapter with GlobalTemporaryTable: FillSchema not working

Question

I'm using FillSchema on a OracleDataAdapter in .net. The SelectCommand works on a global temporary table.

For the first run, this works great. I will get the schema of the global temporary table

Then I drop that temporary table and great a new temporary table with a different schema.

After that, for the second run, the FillSchema method will still return the schema from the old dropped temporary table.

Am I missing something? Shouldn't the select command query the schema from the new version of the temp table?

Thanks for any help!

Perhaps, your _attempted_ `DROP` wasn't successful, as it might be in use! And your create in different schema would have been successful. But when you query second time.. you should have missed the schema alias.. And the synonym(private) still referred the older table! — Maheswaran Ravisankar, Oct 15 '14 at 16:21
but when I check the global temporary table in between, I see that it is gone, after the drop, and I see that it is recreated with the different schema. — DanielG, Oct 15 '14 at 16:26
Hard to confirm without checking your code. And BTW, creating and dropping GTTs on the fly is not an good idea. The data inside GTT is completely local to the session that inserted into it. And there's no need to create it on the fly. — Maheswaran Ravisankar, Oct 15 '14 at 16:29
why is it local? I thought it is a GLOBAL temporary table? I can also see it from sql developer. Thats how I check it — DanielG, Oct 15 '14 at 16:34
The Table is `GLOBAL` .. Where-as the content is local to who inserted it.. — Maheswaran Ravisankar, Oct 15 '14 at 16:34
Unfortunately I'm working here with legacy code and there's no way to change the general logic of the code right now :( — DanielG, Oct 15 '14 at 16:35
Ok, I see. But that should make no difference for the schema right? the schema should be the same for all users — DanielG, Oct 15 '14 at 16:36
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/63119/discussion-between-maheswaran-ravisankar-and-user1682946). — Maheswaran Ravisankar, Oct 15 '14 at 16:37

score 0 · Answer 1 · answered Oct 16 '14 at 08:17

What did not work: The OracleDataAdapter.SelectCommand that was used by FillSchema was:

Select * from TableName

What works: I needed to change that query to the exact schema, then it works:

Select column1,column2,column3 from TableName

I don't understand exactly why, but that solved my problem. It will return the schema of the new global temporary table. Is this a caching problem of the oracle server? Because the tableName is always the same?

score -1 · Answer 2 · edited Dec 22 '15 at 08:07

To use procedure ways to solve this Problem

OracleParameter inputParam = new OracleParameter("TABLE_NAME_IN",OracleDbType.Varchar2,"TEST",ParameterDirection.Input); //Query TableName
OracleParameter refParam = new OracleParameter("OUTPUT",OracleDbType.RefCursor,ParameterDirection.Output);//RefCursor
DataTable dt = new DataTable();//Fill DataTable     
using (OracleCommand dbCommand = new OracleCommand("PKG_SYS.SELECT_TABLE_DATA",orclConnection))
{
    dbCommand.CommandType = CommandType.StoredProcedure;
    dbCommand.Parameters.Add(inputParam);
    dbCommand.Parameters.Add(refParam);
    using (OracleDataAdapter da = new OracleDataAdapter())
    {
        da.SelectCommand = dbCommand;
        da.Fill(dt);
     }
}

OracleDataAdapter with GlobalTemporaryTable: FillSchema not working

2 Answers2