SQL Language (MOSC)

MOSC Banner

ORA-32044 in recursive CTE with cross join clause

edited Jun 4, 2020 6:19AM in SQL Language (MOSC) 5 commentsAnswered

I have recursive CTE. There is a cross join in recursive part of query. Cross-joined table is dummy unnecessary table with single row without any influence on query result. The point is, if cross join clause is used, query fails on "ORA-32044: cycle detected while executing recursive WITH query" error.

with r (v) as (

  select 1 as v from dual

  union all

  select v + 1 from r cross join dual where v < 5

)

select * from r;

If simple comma separation is used, query succeeds.

with r (v) as (

  select 1 as v from dual

  union all

  select v + 1 from r,dual where v < 5

)

select * from r;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center