Oracle create table using with clause

Question

Can I create a table from a query formed using with clause?

Solution 1

Sure:

CREATE TABLE t
AS 
WITH some_data AS ( 
   SELECT 1 as some_value 
   FROM dual

   UNION ALL 

   SELECT 2 
   FROM dual
) 
SELECT * 
FROM some_data

Solution 2

The CREATE TABLE table_name AS statement creates a table based on a select statement. The solution for a with clause will be :

CREATE TABLE t
AS 
SELECT * FROM (
WITH some_data AS ( 
   SELECT 1 as some_value 
   FROM dual

   UNION ALL 

   SELECT 2 
   FROM dual
) 
);

Solution 3

For multiple CTE (common table expressions; i.e. multiple WITH clause), I found the same syntax worked. i.e.

CREATE TABLE schema.table_name as 
WITH table1 as (SELECT 1),
table2 as (SELECT 2)

select * from table2

will create the table_name in the schema from the select statement

Comments

[Nico Haase]

Thats a nearly exact copy of the former answer.... any reason for that?

[Yoav24]

made sense to me more than the first answer... although it's practically the same. thanks.

[Kalin]

What about with multiple defined tables? I.e. if you have "WITH x as (...), y as (...)"?

[Poolka]

Does not work in oracle 12c. Oracle says 'ora-32034: unsupported use of with clause'. Everything after create table t as should be placed inside select * from (...here...). Just like in @saleh helou's answer.

[a_horse_with_no_name]

@Poolka: works for me even on Oracle 11

[Sander Vanden Hautte]

@Poolka I first had the same error, until I realized that the WITH and SELECT within the CREATE statement can apparently not be within parentheses: instead of CREATE TABLE t AS ( WITH some_data AS (...) SELECT * FROM some_data ), it should be CREATE TABLE t AS WITH some_date AS (...) SELECT * FROM some_data.

[Poolka]

@SanderVandenHautte Thank you for the comment. Now i get it. It's so non-intuitive. I used to add outer select query create as (select * from ( with as ... )) to make it work. Like in the other answer here. Never thought that parentheses must or must not be used around statements.