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