Re: INSERT and WITH

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 30 Apr 2009 11:48:46 +0200
Message-ID: <49F973FE.9050508_at_gmail.com>

Hans Mayr schrieb:
> Hello,
>
> I wrote a statement that contains a WITH clause:
>
> SQL A: WITH ... AS adata SELECT ... FROM adata
>
> It works fine. Now I wanted to insert the result into a table
>
> SQL B: INSERT INTO tableA (col1, col2 .....) (WITH ... AS adata
> SELECT ... FROM adata)
>
> While the SQL A works fine, SQL B does not work. In SQL Developer
> simply nothing happens, my DBA tells me that he got the error message
> "ORA-32034: unsupported use of WITH clause".
>
> I also tried something like:
>
> SQL C: WITH ... AS adata INSERT INTO tableA (col1, col2 .....)
> (SELECT ... FROM adata)
>
> But now I get an "ORA-00928: missing SELECT statement".
>
> Is there a solution for this? I did not find documentation on the
> combination of WITH and INSERT.
>
> Thanks in advance.
>
> Hans

Something like this should work:

SQL> create table emp_copy as

   2 select * from emp where 1=2
   3 /

Table created.

SQL> insert into emp_copy

   2 with t as ( select * from emp)
   3 select * from t
   4 /

14 rows created.

SQL> commit

   2 /

Commit complete.

Best regards

Maxim Received on Thu Apr 30 2009 - 04:48:46 CDT