Oracle - Cycle detected while executing recursive 'WITH' query

Question

I'm doing a basic example of recursive query with oracle sql. I'm computing future months of the format MON-YY. I managed to have a seemingly correct query but I don't understand the break condition with a WITH query.

I'm trying to break on the year value (for example stop when you reach 2020), but it detects a cycle while doing that. If I break on the month value (e.g. December), it works.

Here's my query with a month based break:

with
    prochains_mois(mois, annee) as (
        select 'sep' as mois, 19 as annee
        from dual
    union all
        select 
            case mois
                when 'jan' then 'fev'
                when 'fev' then 'mar'
                when 'mar' then 'avr'
                when 'avr' then 'mai'
                when 'mai' then 'jun'
                when 'jun' then 'jui'
                when 'jui' then 'aou'
                when 'aou' then 'sep'
                when 'sep' then 'oct'
                when 'oct' then 'nov'
                when 'nov' then 'dec'
                when 'dec' then 'jan'
            end,
            case mois
                when 'dec' then annee + 1
                else annee
            end
        from prochains_mois r
        where mois <> 'dec'
    )
select * from prochains_mois;

If I do this, it returns a consistent result.

MOI      ANNEE
--- ----------
sep         19
oct         19
nov         19
dec         19

Now if I try to break the recursive query on the year, let's say 2020, so I change the where condition in the with clause to :

where annee < 20

Then I get :

ORA-32044: cycle detected while executing recursive WITH query

I tried to break with a later month to see if my year addition works correctly, it seems to be the case. If I break on march, I get the January and February correctly :

where mois <> 'mar'

gives

MOI      ANNEE
--- ----------
sep         19
oct         19
nov         19
dec         19
jan         20
fev         20
mar         20

Answer 1

Use DATEs:

with prochains_mois( value ) as (
  select DATE '2019-09-01' from dual
union all
  select ADD_MONTHS( value, 1 )
  FROM   prochains_mois
  WHERE  value < DATE '2020-12-01'
)
select SUBSTR( TO_CHAR( value, 'mon', 'NLS_DATE_LANGUAGE=FRENCH' ), 1, 3 ) AS mois,
       TO_CHAR( value, 'RR' ) AS annee
from   prochains_mois;

Output:

MOIS | ANNEE
:--- | :----
sep  | 19   
oct  | 19   
nov  | 19   
dec  | 19   
jan  | 20   
fev  | 20   
mar  | 20   
avr  | 20   
mai  | 20   
jui  | 20   
jui  | 20   
aou  | 20   
sep  | 20   
oct  | 20   
nov  | 20   
dec  | 20   

or use your query and check that the month and year do not match:

with
    prochains_mois(mois, annee) as (
        select 'sep' as mois, 19 as annee
        from dual
    union all
        select 
            case mois
                when 'jan' then 'fev'
                when 'fev' then 'mar'
                when 'mar' then 'avr'
                when 'avr' then 'mai'
                when 'mai' then 'jun'
                when 'jun' then 'jui'
                when 'jui' then 'aou'
                when 'aou' then 'sep'
                when 'sep' then 'oct'
                when 'oct' then 'nov'
                when 'nov' then 'dec'
                when 'dec' then 'jan'
            end,
            case mois
                when 'dec' then annee + 1
                else annee
            end
        from prochains_mois r
        where ( mois, annee ) NOT IN ( ( 'dec', 20 ) )
    )
select * from prochains_mois;

Output:

MOIS | ANNEE
:--- | ----:
sep  |    19
oct  |    19
nov  |    19
dec  |    19
jan  |    20
fev  |    20
mar  |    20
avr  |    20
mai  |    20
jun  |    20
jui  |    20
aou  |    20
sep  |    20
oct  |    20
nov  |    20
dec  |    20

db<>fiddle here