[Closed] SQLServer to Oracle help

No you can't.
Global temp tables are session based tables.
These tables need to be defined beforehand.
The scope of data in these tables is for the duration of the session and only visible to the session.

Can I ask more about what it is you are trying to accomplish? As there maybe other ways of doing it.

Just been handed a Oracle script to be converted to run in SQLServer.

The script was missing the table def's where data is temporarly stored during processing (upto 1mil row). So to get it working in SQLServer I changed the 'INSERT' statement to a 'SELECT INTO' statement. It's all running now but i would like to complete the Oracle version of this script. By your reply, I will need to pre define the tables at the beginning.

Dont really want to use views as a number of queries are run over the temp tables.

Any advice is much appreciated.

Just been handed a Oracle script to be converted to run in SQLServer.

So, it's an Oracle script already? So why do you need to do any converting (to Oracle) on it?

Dont really want to use views as a number of queries are run over the temp tables.

The use of traditional "Views" will give you performance degradation.
Look into materialized views.

Looks like the use of a staging table or other design-time database object is unavoidable.

If you want to do the database object creation on-the-fly you might also want to look into the EXECUTE IMMEDIATE command.

Be careful with "select * into" this can (but not always) result in sql server aggressively escalating the locks to table locks on the source tables. Also you end up with no indexes on your temp tables so any queries will likely be table scans (= bad performance). Bad performance on queries running in temp db can have an effect on everything else to do with this server.

Background. I work on a system that can be run on either SQLServer or Oracle.

Current site is SQLServer but one of the BA's here gave me a Oracle script they had from another site and wanted it run here.

The oracle script was incomplete as it didn't contain the table defs for the work/staging tables so I used the #temptable ability of SQLServer. I don't need to run it in Oracle now just wanted to include some tips in the script for when the BA pulls it out again on a oracle site the developer will have a head start.

In Oracle you can do

CREATE TABLE temp1 AS select x,y,z from temp2 ....

This will work on its own - but check out the full syntax to ensure you create the table in the right tablespace. You can even create the table as a session based table (a la SQL Server) that automatically gets dropped on close of session.

[url= http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html ]Syntax Linky[/url]

Sounds like you need to run it as a sql script rather than something that belongs in the database schema.

bol.

I seem to remember (and I'm not really up to speed on oracle these days) you can create global temporary tables in oracle. You will just need declare the tables first. But you should be able to script out most of that, saving you typing it all out.

This look like the ticket.

A session based CREATE TABLE temp1 AS select x,y,z from temp2 ....

Thanks very much folks.