Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE date_period(dt_from DATE, dt_to DATE);
INSERT INTO date_period(dt_from, dt_to) VALUES (DATE '2023-02-13', DATE '2023-02-15');
1 rows affected

INSERT INTO date_period(dt_from, dt_to) VALUES (DATE '2023-02-14', DATE '2023-02-18');
1 rows affected

INSERT INTO date_period(dt_from, dt_to) VALUES (DATE '2023-06-20', DATE '2023-06-22');
1 rows affected

SELECT dt_from + LEVEL - 1 AS dates
FROM date_period
CONNECT BY LEVEL <= dt_to - dt_from + 1
AND PRIOR dt_from = dt_from
AND PRIOR SYS_GUID() IS NOT NULL;


DATES


13-FEB-23

14-FEB-23

15-FEB-23

14-FEB-23

15-FEB-23

16-FEB-23

17-FEB-23

18-FEB-23

20-JUN-23

21-JUN-23

22-JUN-23


/*
Using a hierarchical query with the CONNECT BY clause 
to generate the dates between dt_from and dt_to for each row in the table. 
The PRIOR conditions used to prevent the query from generating a Cartesian product.
*/
ORA-01742: comment not terminated properly

WITH date_range (dt_from, dt_to) AS (
  SELECT dt_from, dt_to FROM date_period
  UNION ALL
  SELECT dt_from + 1, dt_to FROM date_range WHERE dt_from < dt_to
)
SELECT dt_from AS dates FROM date_range ORDER BY dt_from;


DATES


13-FEB-23

14-FEB-23

14-FEB-23

15-FEB-23

15-FEB-23

16-FEB-23

17-FEB-23

18-FEB-23

20-JUN-23

21-JUN-23

22-JUN-23


/*
This recursive CTE generates the dates between dt_from and dt_to for each row in the table 
by repeatedly adding one day to dt_from until it is equal to dt_to.
*/
ORA-01742: comment not terminated properly