INSERT … SELECT : missing SELECT keyword-CodePudding

I am experimenting with a recursive CTE to split a string into multiple values. The data is then inserted into another table.

The following works in PostgreSQL:

CREATE TABLE test(data varchar(255));

WITH RECURSIVE
    cte(genres) AS (SELECT 'apple,banana,cherry,date'),
    split(genre,rest,genres) AS (
        SELECT '', genres||',',genres FROM cte
        UNION ALL
        SELECT
            substring(rest,0,position(',' IN rest)),
            substring(rest,position(',' IN rest) 1),
            genres
        FROM split WHERE rest<>''
    )
INSERT INTO test(data)
SELECT genre
FROM split;

However, the Oracle version:

CREATE TABLE test(data varchar(255));
WITH
    cte(genres) AS (SELECT 'apple,banana,cherry,date' FROM dual),
    split(genre,rest,genres) AS (
        SELECT '', genres||',',genres FROM cte
        UNION ALL
        SELECT
            substr(rest,1,instr(rest,',')-1), 
            substr(rest,instr(rest,',') 1),
            genres
        FROM split WHERE rest IS NOT NULL
    )
INSERT INTO test(data)
SELECT genre
FROM split WHERE genre IS NOT NULL;

gives me the error message:

ORA-00928: missing SELECT keyword.

Now I’m pretty sure that I’ve got one, there between the INSERT and the following FROM.

If you comment out the INSERT, the rest of it will give the results. Elsewhere, I know that a simple INSERT … SELECT does work.

Is it something to do with the recursive CTE? How can I get this to work properly using a standard recursive CTE?

Using Oracle 18c in a Docker image. There is a fiddle here: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=d24f3105634933af1b7072bded1dacdf .

CodePudding user response：

An INSERT-SELECT means "INSERT" command first, then the SELECT part, and the WITH is part of the SELECT not the insert.

SQL> create table t ( x int );

Table created.

SQL> with blah as ( select 1 c from dual)
  2  insert into t
  3  select * from blah;
insert into t
*
ERROR at line 2:
ORA-00928: missing SELECT keyword


SQL> insert into t
  2  with blah as ( select 1 c from dual)
  3  select * from blah;

1 row created.