How to make recursion for each period without eternal loop?

Question

My query stuck in eternal loop when trying to recursively calculate period for every each plan_period. Below is the what I have tried:

WITH p (period_id, start_period, end_period  )
         AS (SELECT 
                    PERIOD_VERSIO,
                    To_date(FROM_PERIOD, 'YYYYMM'),
                    To_date(TO_PERIOD, 'YYYYMM')
             FROM   SOR_MAP_PLAN_PERIOD),
         date_ranges
         AS (SELECT 
                   distinct
                    period_id,
                    To_char(start_period, 'YYYYMM') as START_PERIOD,
                    To_char(Add_months (start_period, LEVEL - 1), 'YYYYMM') as END_PERIOD
             FROM   p
             CONNECT BY LEVEL <= Months_between( end_period, start_period ) + 1)


    SELECT d.period_id as "KEY",
        --   d.start_period,
           d.end_period as PERIOD,
         e.end_period as PLAN_PERIOD
    FROM   date_ranges d
    --order by 3,2

          full outer join (SELECT period_id,end_period
                       FROM   date_ranges
                       ) e on e.period_id=d.period_id
    ORDER  BY 3,  2, 1

    ; 
Parameter table:

    PERIOD_VERSIO   FROM_PERIOD TO_PERIOD
    201612_BU       201701      201712
    201705_STR      201801      202012
    201706_SRT1     201801      202012
    201709_LE       201710      201809

Wanted result:

PERIOD_VERSIO   PERIOD  PLAN_PERIOD
201612_BU       201701  201701
201612_BU       201702  201701
201612_BU       201703  201701
201612_BU       201704  201701
201612_BU       201705  201701
201612_BU       201706  201701
201612_BU       201707  201701
201612_BU       201708  201701
201612_BU       201709  201701
201612_BU       201710  201701
201612_BU       201711  201701
201612_BU       201712  201701
201612_BU       201701  201702
201612_BU       201702  201702
201612_BU       201703  201702
201612_BU       201704  201702
…..     
…..     
201706_SRT1     201801  201801
201706_SRT1     201802  201801
201706_SRT1     201803  201801
201706_SRT1     201804  201801
201706_SRT1     201805  201801
201706_SRT1     201806  201801
201706_SRT1     201808  201801
201706_SRT1     201808  201801
201706_SRT1     201809  201801
201706_SRT1     201810  201801
201706_SRT1     201811  201801
201706_SRT1     201812  201801
……      
201706_SRT1     201801  202012
201706_SRT1     201802  202012
201706_SRT1     201803  202012
201706_SRT1     201804  202012
201706_SRT1     201805  202012
201706_SRT1     201806  202012
201706_SRT1     201808  202012
201706_SRT1     201808  202012
201706_SRT1     201809  202012
201706_SRT1     201810  202012
201706_SRT1     201811  202012
201706_SRT1     201812  202012

Answer 1

Your query could be as easy as this:

select period_versio
      ,ym as period
      ,from_period plan_period
  from parameter_t a  
  join periods_t   b on(
       b.ym >= a.from_period 
   and b.ym <= a.to_period
  );

...if you had a table of periods...which you should probably have if you intend to work a lot with them. Here is how to create a table of periods from years 2000-2030.

create table periods_t(
   ym    number not null
  ,year  number not null
  ,month number not null
  ,primary key(ym)
);

insert 
  into periods_t(ym, year, month)
select year || lpad(month, 2, '0') as ym
      ,year
      ,month
  from (select 1999 + level as year from dual connect by level <= 30) y
      ,(select level as month       from dual connect by level <= 12) m;

I have assumed the following table structure from example parameters

create table parameter_t(
   period_versio varchar2(20) not null
  ,from_period   number(6)    not null
  ,to_period     number(6)    not null
);

insert into parameter_t values('201612_BU',     201701, 201712);
insert into parameter_t values('201705_STR',    201801, 202012);
insert into parameter_t values('201706_SRT1',   201801, 202012);
insert into parameter_t values('201709_LE',     201710, 201809);

Answer 2

This can be done in pure SQL.

First we have a recursive WITH clause which generates all the months spanned by the start and end periods for each period_versio'. Then there's join two instances of that subquery which produces all the permutations of generated months for theperiod_versio'.

with mths (period_id, period_st, period_end, lvl) as (
    select period_versio, 
           cast(to_date(from_period, 'yyyymm') as date), 
           cast(to_date(to_period, 'yyyymm') as date), 
           1 
    from sor_map_plan_period
    union all
    select period_id, 
           cast(add_months(period_st, 1) as date), 
           cast(period_end as date), 
           lvl+1
    from mths
    where lvl < months_between(period_end, period_st)
)   
select t1.period_id as period_versio
       ,to_char( t1.period_st, 'yyyymm') as period
       ,to_char( t2.period_end, 'yyyymm') as plan_period
from       
( select period_id,
       period_st
  from   mths) t1
join 
(select period_id,
             period_end
 from   mths) t2
on t1.period_id = t2.period_id
order by 1, 2, 3
/

---

The recursive WITH clause was introduced in Oracle 11gR2. It has a nasty bug when passing dates, hurling ORA-01790. Hence the need for the odd-looking CAST expressions around things which are apparently already dates. If you're working on 12c you may not need them.