Kevin,
First piece of advice, use SELECT ... INTO. Whoever told you it doesn't work is incorrect. I don't remember 7.3 but I know for a fact it works in 8i and 9i (I use it all the time).

As for changing the minds of the execs, why do they think it is more cost effective if you do stuff in the middle-tier. If a change needs to occur in the back-end for any of the following reasons:

upgrade of Oracle/SQL Server

calculations for report changes

table structures change

If any of these occur, you can change the SP without ever having to deploy a new middle-tier. Since the database is centralized, you only have to do the changes in one place and you can do it remotely. If you change the middle-tier, you have to deploy it everywhere.

Good luck!

>Larry,
>
>Thanks for your answers, especially on the GLOBAL TEMPORARY KEYWORDS. I also picked up SQL IN A NUTSHELL by O'Reilly Press, which does a decent job of covering the differences in implementation of specific commands.
>
>The way we planned to use #TEMPTABLES was just for the duration of the transaction or query process. Again, if a user has selected 10 accounts and 100 products for a report (or 50 accounts/2 products, etc.), we build #TEMPTABLES in SQL containing the keys for the accounts/items selected by the user. The query does a JOIN against the #TEMPTABLES, and then DROPS the #TEMPTABLES at the end. Since #TEMPTABLES are specific to the connection/user, we can have dozens of users doing this at once.
>
>(The #TEMPTABLES only last for a few seconds...they're basically a way of telling SQL Server which accounts and items to join on. I get dozens of questions asking, 'why don't you just include the accounts and items in-line in the query?' Problem is that the join could be on hundreds of accounts/items.)
>
>From what you're telling me, we can do the same general thing in Oracle, except with different syntax. I can live with coding different syntax in the middle-tier, so long as the general approach we're taking can be the same.
>
>Unfortunately, I just learned that SELECT...INTO doesn't exist in Oracle. That one hurts, because I was using SELECT...INTO during a query process to bring back multiple result sets...
>
>Yes I agree with your suggestion, we need to check for the presence of the tablename before creating it.
>
>On the question of using SPs...I'd love to, even just for those areas in Oracle / SQL Server that are the same. The problem is that some of our potential business partners who have had to support Oracle and SQL Server have spooked our company president and senior VP from using stored procs. Usage of them in the database back-end has been equated with non-portability. Our project/database managers (most of whom have SQL Server clients, but one or two may have Oracle clients in the next year) have said that they won't participate in managing different stored procs for different back-ends...that the developers should code for it in the middle-tier.
>
>So we have to do all the database stuff in the middle-tier with pass-through, etc. It sucks, it royally sucks, and I'm totally ticked off that I'm being told basically that I can't optimize for performance. But...that's the situation I'm in. If you have any advice, I'm all ears!
>
>Thanks again,
>Kevin

Larry Miller
MCSD
LWMiller3@verizon.net

Accumulate learning by study, understand what you learn by questioning. -- Mingjiao