Geek Answers Handbook

How to use Oracle global temporary table?

I am trying to use an Oracle global temporary table without physically creating the table in the database. The following code does not work. Can someone explain how to use global temporary tables properly?

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;
+4
source share
3 answers

Try using the execute method immediately: it uses an exception handler to get around if the 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;
+4
source

EXECUTE IMMEDIATE, PL/SQL. :

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;
+3

Oracle global global tables are slightly different than expected.

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

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

+3
source

More articles:


All Articles