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

<kgoff_at_worldnet.att.net> wrote in message news:14Eb9.1427$jG2.96165_at_bgtnsc05-news.ops.worldnet.att.net...
> So the application needs to collect the user selections for
accounts/items,
> build temporary tables in the back-end database that represent the list of
> accts/items that the user selected, and then run a SQL statement that
joins
> the back-end invoice table with the account/product temp tables that,
again,
> represent what the user selected.

Its highly likely that you don't need to use a temporary table at all in Oracle, just write a sql query that looks at the accounts/items/invoice tables directly. However if you do decide to use a temporary table then you should create a global temporary table when you install your schema. These come in two varieties ON COMMIT PRESERVE ROWS and ON COMMIT DELETE ROWS.

However the prime reasons for using temporary tables in SQL Server seem to me to be about consistency and locking issues, both of these problems essentially go away on an Oracle platform. Just query the data.

>
> The code that I posted represents a stripped down version of what we need
to
> do. It isn't 'SQL Server' logic...it represents the only way that I know
to
> address this reporting requirements. Much of our reporting requirements
> involve things that are variable at run-time. So my question is...will
this
> code run without modification when connected to SQL Server, or is the
syntax
> for creating temp tables a bit different?

I'd regard it as SQL server logic, at least there is no way that I would sign off on a piece of code that went around creating tables on the fly.

>
> If you know of a better way to address this reporting requirement, I'd be
> very interested to know what it is.

I'd suggest that you at least consider using managed providers for sql server and oracle if you are going down the dot net route (as opposed to OLEDB) they both seem stable and perform well. I'd then also create two sets of stored procedures (one sql, one oracle) and interface to the SP from your web code.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************