Recursive CTE gives "Error at line 1/16: ORA-00905: missing keyword" error on Oracle SQL-CodePudding

    WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n   1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

I copied the above code from Oracle documentation. It works fine when I run it in MySQL, but I get the error message "Error at line 1/16: ORA-00905: missing keyword" when I run it in Oracle's Apex workshop. It is strange that a sample from Oracle documentation does not work. Any idea what the problem is?

CodePudding user response：

It is syntactically invalid to have RECURSIVE in the query. Whichever documentation you used it was not for an Oracle database; you want this documentation.

Additionally, SELECT 1 is not valid as you have a SELECT without a FROM clause; it should be SELECT 1 FROM DUAL.

The fixed code should be:

WITH cte (n) AS
(
  SELECT 1 FROM DUAL
  UNION ALL
  SELECT n   1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

Which outputs:

N
1
2
3
4
5

fiddle

CodePudding user response：

In oralce, you can't use SELECT 1, that is only allowed in Mysql

As is aid ORACLE rdms is not Mysql RDMS

select * from V$VERSION;

BANNER	BANNER_FULL	BANNER_LEGACY	CON_ID
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production	Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0	Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production	0

    WITH  cte (n) AS
(
  SELECT 1 FROm DUAL
  UNION ALL
  SELECT n   1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

N
1
2
3
4
5

fiddle