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

Fatih Atalay 0
Viewed 34
    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?

oracle sql
2 Answers

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

MT00

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

nbk0
Related