How to use Oracle global temporary table?

Question

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 1

Try the below using execute immediate: it uses exception handler to bypass if table already exists; also note that you cannot use SQL select inside PLSQL

DECLARE
  l_column1 number;
begin
  begin
    execute immediate 'create global temporary table my_temp_table(column1 number) 
on commit   preserve rows';
  exception when others
    then
    dbms_output.put_line(sqlerrm);
  end;
  insert into my_temp_table (column1) values (1);
  select * into l_column1 from my_temp_table where column1=1;
  dbms_output.put_line('the temp value is '||l_column1);   
end;

Answer 2

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;   

Answer 3

Oracle global temp tables are a bit different than you might be expecting.

You need to create the table and declare it as a global temp table.

Here is a good resource: http://www.oracle-base.com/articles/misc/temporary-tables.php