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;

Global temporary tables have to be created in the database, they can't be defined locally in PL/SQL. There are other ways to hold data in PL/SQL, using [collections](http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS00501). The appropriate tool depends on what you're trying to do - why do you want a GTT in the first place? What's is your end goal for the data you're putting in it? — Alex Poole, Dec 20 '13 at 15:31
@AlexPoole:[link](http://docs.oracle.com/javadb/10.6.2.1/ref/rrefdeclaretemptable.html) there is a feature to declare temporary table for session ,please find the link,seen i am not sure how this is been used ,but love to explore — Gaurav Soni, Dec 20 '13 at 15:33
@GauravSoni - that's documentation for Derby, not Oracle RDBMS? — Alex Poole, Dec 20 '13 at 15:34
@AlexPoole: Ahh so sorry ,may be Matthew is also confused because of that . — Gaurav Soni, Dec 20 '13 at 15:38

score 4 · Answer 1 · answered Dec 20 '13 at 15:30

4

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;

answered Dec 20 '13 at 15:30

Wernfried Domscheit

54,457
9
76
110

oracle-base.com article reference in comment 1 above then this explains its use with the example that what happens when the session is connected again after disconnecting it. Its data will be purged as a result. – Pankaj Jan 12 '19 at 05:14
Primary benifit of using it will be reducing the redo getting generated. undo will be still the same as of conventional table. – Pankaj Jan 12 '19 at 05:16

score 4 · Answer 2 · answered Dec 20 '13 at 15:30

4

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

answered Dec 20 '13 at 15:30

Brian McGinity

5,777
5
36
46

score 4 · Accepted Answer · edited Aug 24 '16 at 11:13

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;

How to use Oracle global temporary table?

3 Answers3

Linked