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

Hans Mayr schrieb:
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

Well, Thanks a lot. So there is only one version of the INSERT syntax
that works with WITH. Very logical.

Best,

Hans