Kevin,
The stuff will work but you will have to make syntax changes in the DDL command for Create Table. If you use the same syntax on Oracle that works on SQL Server, the table will be created but it will not be temporary. It will be a normal table with the data being visible by all connected to Oracle at that time. Change it using the GLOBAL TEMPORARY keywords and it shoulod work for you.

However, you will need to run a test to see if the table already exists before attempting to recreate it. You can do a select out of USER_TABLES (dynamic Oracle view) for the table name. If it exists, don't create it; truncate it.

I do have some questions.

Does this approach work in SQL Server? I thought temp tables lasted for the length of the currently running context. In this case, the temp table would get created and immediately be deleted. The work around is to use ##TempTableName. This creates a temp table that persists across contexts and is visible to all users. The table is simply created in TempDB. not in the active database. The problem with this is that data would also be shared across all users. Maybe it is ADO that is keeping the context active and you don't need the global operator. Just wondering.

Why wouldn't you use SPs? Even if you need to support Oracle and SQL Server, you can still make use of SPs, IMO. The middle-tier would basically have the same SPT/ADO syntax (with some minor changes). You know how to write code in T-SQL (SQL Server) and PL/SQL (Oracle) already because you need to pass those commands via the Commadn object. Why not make use of the power of the backend?


>Hi, Larry...
>
>Thanks for your response. Let me ask this a different way...we need to support both SQL Server and Oracle, so we're doing as much in the middle tier as possible. We're not using any stored procs...
>
>What some of this boils down to is the following...will the following code work in Oracle?
>
>MyConn = OleDbConnection("connect string for either Oracle or SQL");
>MyConn.Open();
>OleDBCommand oCmd = new OleDbCommand("CREATE TABLE #GeoList (GeoKey INTEGER)",MyConn);
>oCmd.ExecuteNonQuery();
>
>OleDbCommand oCmd2 = new OleDbCommand("INSERT INTO #GeoList (GeoKey) VALUES (100)");
>oCmd2.ExecuteNonQuery();
>
>MyAdapter = new OleDBAdapter("SELECT * FROM History JOIN #GeoList ON History.GeoKey = #GeoList.GeoKey",MyConn);
>DsReturn = new DataSet();
>MyAdapter.Fill(DsReturn,"myhistoryextract");
>MyConn.Close();
>
>This type of code runs fine when connecting to SQL Server...my question is whether it would run equally fine when connected to Oracle.
>
>If so, that's great. If I'd have to make minor tweaks to the syntax, so be it. But if this kind of approach can't be done, I'm totally screwed!
>
>Being able to have the app create temp tables on the fly, based on user selections, before running a report is a requirement of our app.
>
>Kind of makes me wish I could use FoxPro again! ;)
>
>Thanks,
>Kevin