Re: Will the following code run when connected to Oracle?

Hi There,

Oracle and SQL Server are two quite different beasts. My first recommendation would be to "forget" about SQL Server logic, learn up about how Oracle works and code according to what works best in Oracle. Applying SQL Server logic in an Oracle database is like determining how best to trim the sails in a space shuttle. Such code invariably is inefficient and non scalable.

It is most unlikely that you would ever need to create temporary tables "on the fly" in Oracle (because the locking and consistency rules in Oracle are so different).

Check out http://otn.oracle.com (free subscription), check out the documentation set, checkout specifically the Concepts manual and start doing things the Oracle way.

Cheers

Richard
<kgoff_at_worldnet.att.net> wrote in message news:MRBb9.42332$Ke2.2960505_at_bgtnsc04-news.ops.worldnet.att.net...
> I have a question about creating temporary tables 'on the fly' in the
> middle-tier. In our app, users select lists of accounts and products as
a
> part of running reports. Basically, I take the key(s) for each
> account/product that the users select, build temp tables in SQL Server,
and
> then perform a query with a JOIN against the temp tables that were
created.
> I know that the following code (using .NET, written in C#) will work when
> running against SQL Server. I'd like to know how much modification I'd
> need to do to support use of 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();
>
> I've read that temp tables are created somewhat differently in Oracle. If
> it's just a matter of minor syntax changes in the CREATE TABLE #TEMP
> statement, I can obviously account for differences in syntax. Any
> thoughts?
>
> Thanks,
> Kevin
Received on Fri Aug 30 2002 - 06:56:08 CEST