Migrating from SQL Server to ORACLE ¿what to do with temporary tables?

Question

Currently we use SQL Server and we have A LOT (read around 5.000) different scripts that create on the fly temporary tables.

Now we are migrating to ORACLE, so we cannot create on the fly temporary tables.

Any ideas?

Thanks in advance

Answer 1

Oh boy, that is a lot of temporary tables.

Have you had a look at Oracle's SQL Developer tool? It's free and it comes with a Migration Workbench which can help you with the journey.

With regards to temporary tables it appears that the OMWB will create temporary tables from the T-SQL statements. Find out more.

Caveat: I have never undertaken such a migration myself so I am not guaranteeing it. But with 5000 scripts to migrate it has to be worth your while to evaluate it.

Answer 2

What about Oracle Global Temporary Tables?

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS; -- or use ON COMMIT PRESERVE ROWS to keep data until the end of your session.