loop | easy to generate 'loop ranges'?

need a column starting 1 to 100:

    SELECT  LEVEL SL_NO
      FROM  DUAL
CONNECT BY  LEVEL <= 100;

what about dates, from a date to a date?

    SELECT  TO_DATE(:from_date, 'DD-MON-RRRR') + LEVEL - 1 SL_NO
      FROM  DUAL
CONNECT BY  LEVEL <= (TO_DATE(:to_date, 'DD-MON-RRRR') - TO_DATE(:from_date, 'DD-MON-RRRR'));

example use of date loop:

DECLARE
    l_FROM_DATE DATE;
    l_TO_DATE   DATE;
BEGIN
    l_FROM_DATE := TO_DATE('01/11/2021', 'DD/MM/RRRR');
    l_TO_DATE   := TO_DATE('09/11/2021', 'DD/MM/RRRR');

    FOR x IN (      SELECT  (l_FROM_DATE + LEVEL - 1) V_DT
                      FROM  DUAL
                CONNECT BY  LEVEL <= (l_TO_DATE - l_FROM_DATE)
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(TO_CHAR(x.V_DT, 'DD-MON-RRRR'));
    END LOOP;
END;

/*
01-NOV-2021
02-NOV-2021
03-NOV-2021
04-NOV-2021
05-NOV-2021
06-NOV-2021
07-NOV-2021
08-NOV-2021
09-NOV-2021
*/

Blog

loop | easy to generate 'loop ranges'?

weathered

Join Our Newsletter. No Spam, Only the good stuff.

Related