Cannot Create PRIVATE TEMPORARY TABLE in Oracle SQL Developer v19.0.1 (error: ORA-00905)

user_11698850 0
Viewed 2376

I am trying to create a temporary table in Oracle SQL Developer (v.19.0.1), but it is throwing error: ORA-00905: missing keyword

CREATE PRIVATE TEMPORARY TABLE sales AS

SELECT SalesDate, Product, Quantity
FROM BI.Sales a
JOIN PRODREF.SKU b on a.item_ID = b.sku
WHERE SalesDate >= '01-JUN-19';

Error report - ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause:
*Action:

oracle
1 Answers

Private temporary tables (PTTs) are an Oracle Database 18c feature. Oracle SQL Developer is a separate product and has its own version numbers.

So first ensure the database you're using is on this release or higher:

select banner from v$version;

BANNER                                                                   
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Next, you must prefix the name of PTTs with whatever the private_temp_table_prefix parameter is set to:

sho parameter private

NAME                      TYPE   VALUE    
------------------------- ------ -------- 
private_temp_table_prefix string ORA$PTT_

ORA$PTT_ is the default. So your create table should probably be:

CREATE PRIVATE TEMPORARY TABLE ora$ptt_sales AS
  SELECT SalesDate, Product, Quantity
  FROM BI.Sales a
  JOIN PRODREF.SKU b on a.item_ID = b.sku
  WHERE SalesDate >= '01-JUN-19';
user_14859550
Related