CODEWITHSUNDEEP
oracle-database
albajoin
albajoin

Reputation: 51

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

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:

Upvotes: 2

Views: 4923

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9865

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

Upvotes: 3

Related Questions