If the amount of data to be processed or utilized from your PL/SQL procedure is too large to fit in a PL/SQL table, use a GLOBAL TEMPORARY table rather than a normal table.
A GLOBAL TEMPORARY table has a persistent definition but data is not persistent and the global temporary table generates no redo or rollback information.
For example if you are processing a large number of rows, the results of which are not needed when the current session has ended, you should create the table as a temporary table
create global temporary table temp_table(name varchar2(30)) on commit preserve rows;
The “on commit preserve rows” clause tells the SQL engine that when a transaction is committed the table should not be cleared.
if the data is not needed you should use “on commit delete rows”
The global temporary table will be created in the users temporary tablespace when the procedure populates it with data
-- Example
create global temporary table temp_table(name varchar2(30)) on commit preserve rows;
Table created.
insert into temp_table values('John');
1 row created.
commit;
Commit complete.
select * from temp_table
2 /
NAME
------------------------------
John
on commit the data is available for selection in the same session
create global temporary table temp_table(name varchar2(30)) on commit delete rows;
Table created.
insert into temp_table values('John');
1 row created.
select * from temp_table;
NAME
------------------------------
John
commit;
Commit complete.
select * from temp_table;
no rows selected
on commit the data is not available for selection in the same session
Tags:
Oracle