CODEWITHSUNDEEP
oracle-databasetemp-tablestoad
user15676
user15676

Reputation: 153

Toad of Oracle - create private temporary tables

Trying to create private temp table but keep getting error msg, [Error] Execution (1: 16): ORA-00905: missing keyword

The word TEMPORARY is underline in red and when I hover over it is says Found 'TEMPORARY' Expecting: 'OUTLINE' what does this mean and is this causing the error?

CREATE PRIVATE TEMPORARY TABLE ora$ptt_orders AS
(
 select * from orders 
 where rownum < 10
)
ON COMMIT PRESERVE DEFINITION;

Upvotes: 2

Views: 5140

Answers (1)

Littlefoot
Littlefoot

Reputation: 143003

You can't create a private temporary table in your database version (as you've been told).

But, you can create a global temporary table instead. Using as select, syntax is like in this example:

SQL> create global temporary table ptt_orders
  2  on commit preserve rows
  3  as
  4  select * from emp
  5  where deptno < 50;

Table created.

SQL>

You can choose what to do with rows upon commit - to preserve them, or to delete them. Difference is whether table contents is available to you during session or transaction.

Upvotes: 1

Related Questions