How can we use oracle private temporary tables in a pl/sql block?

Question

I see the concept of temporary table in oracle is quite different from other databases like SQL Server. In Oracle, we have a concept of global temporary table and we create it only once and in each session we fill it with data which is not the same in other databases.

In 18c, oracle has introduced the concept of private temporary tables which states that upon successful usage, tables can be dropped like in other databases. But how do we use it in a PL/SQL block?

I tried using it using dynamic SQL - EXECUTE IMMEDIATE. But it is giving me table must be declared error. what do I do here?

Answer 1

It works with dynamic SQL:

declare 
  cnt int; 
begin 
  execute immediate 'create private temporary table ora$ptt_tmp (id int)'; 
   
  execute immediate 'insert into ora$ptt_tmp values (55)'; 
  execute immediate 'insert into ora$ptt_tmp values (66)'; 
  execute immediate 'insert into ora$ptt_tmp values (77)'; 
   
  execute immediate 'select count(*) from ora$ptt_tmp' into cnt; 
  dbms_output.put_line(cnt); 
   
  execute immediate 'delete from ora$ptt_tmp where id = 66'; 
  cnt := 0; 
   
  execute immediate 'select count(*) from ora$ptt_tmp' into cnt; 
  dbms_output.put_line(cnt); 
end;

Example here: https://livesql.oracle.com/apex/livesql/s/l7lrzxpulhtj3hfea0wml09yg

Answer 2

Private temporary tables (Available from Oracle 18c ) are dropped at the end of the session/transaction depending on the definition of PTT.

• The ON COMMIT DROP DEFINITION option creates a private temporary table that is transaction-specific. At the end of the transaction, Oracle drops both table definitions and data.
• The ON COMMIT PRESERVE DEFINITION option creates a private temporary table that is session-specific. Oracle removes all data and drops the table at the end of the session.

You do not need to drop it manually. Oracle will do it for you.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_table (
  ......
)
ON COMMIT DROP DEFINITION; 
-- or
-- ON COMMIT PRESERVE DEFINITION; 

Example of ON COMMIT DROP DEFINITION (table is dropped after COMMIT is executed)

Example of ON COMMIT PRESERVE DEFINITION (table is retained after COMMIT is executed but it will be dropped at the end of the session)

Note: I don't have access to 18c DB currently and db<>fiddle is facing some issue so I have posted images for you.

Cheers!!

Answer 3

But how do we use it in a PL/SQL block?

If what you mean is, how can we use private temporary tables in a PL/SQL program (procedure or function) the answer is simple: we can't. PL/SQL programs need to be compiled before we can call them. This means any table referenced in the program must exist at compilation time. Private temporary tables don't change that.

The private temporary table is intended for use in ad hoc SQL work. It allows us to create a data structure we can use in SQL statements for the duration of a session, to make life easier for ourselves.

For instance, suppose I have a massive table of sales data - low level transactions - and my task is to investigate monthly trends. So I only need the total sales by month. Unfortunately, there is no materialized view providing this summary. I don't want to include the aggregating query in my select statements. In previous versions I would have had to create a permanent table (and had to remember to drop it afterwards) but in 18c I can use a private temporary table to stage my summary just for the session.

create private temporary table ora$ptt_sales_summary (
sales_month date
, total_value number )
/

insert into ora$ptt_sales_summary
select trunc(sales_date, 'MM')
       , sum (qty*price)
from massive_sales_table
group by trunc(sales_date, 'MM')
/

select * 
from ora$ptt_sales_summary
order by sales_month
/

Obviously we can write anonymous PL/SQL blocks in our session but let's continue assuming that's not what you need. So what is the equivalent of a private temporary table in a permanent PL/SQL program? Same as it's been for several versions now: a PL/SQL collection or a SQL nested table type.