How to use Oracle global temporary table?

Deep in Development picture Deep in Development · Dec 20, 2013 · Viewed 44.5k times · Source

I am attempting to use an Oracle global temporary table without physically creating a table in the database. The following code is not working. Can someone please explain the proper way to use global temporary tables?

declare
  global temporary table my_temp_table(column1 number) on commit preserve rows;    
begin
  insert into my_temp_table (column1) values (1);
  select * from my_temp_table;   
end;

Answer

Wernfried Domscheit picture Wernfried Domscheit · Dec 20, 2013

Unless you use EXECUTE IMMEDIATE you cannot create the table inside PL/SQL. Try this:

create global temporary table my_temp_table(column1 number) on commit preserve rows;    

insert into my_temp_table (column1) values (1);
select * from my_temp_table;