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

Currently we use SQL Server and we have A LOT (read around 5.000) different scripts that 开发者_如何学Ccreate 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

You'd probably want to dynamically create the tables with an execute immediate whenever you need a temporary table:

-- creating the table
begin 
  execute immediate q'!
    create table tmp_foo_bar ( 
       col_1 number,
       col_2 varchar2(50),
       etc   date
     ) !';
end;
/

-- using the table:
insert into tmp_foo_bar values (42, 'forty-two', sysdate);

-- dropping the table:
begin
  execute immediate 'drop table tmp_foo_bar';
end;
/

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.

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.