Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE date_dim( 
                      start_date DATE, 
                      dayofweek INT,
                      dayofmonth INT,
                      dayofyear INT,
                      dayname VARCHAR2(35),
                      month INT,
                      monthname VARCHAR2(35), 
                      year INT,
                      quarter INT
                     ) 
CREATE OR REPLACE PROCEDURE pop_date_dim(DDMMYYYY IN varchar2, n IN number) IS
  start_date DATE;
BEGIN
  start_date := TO_DATE(DDMMYYYY, 'DDMMYYYY');  
  FOR i IN 1 .. n LOOP
    INSERT INTO date_dim
    VALUES
      (start_date + i,
       TO_CHAR(start_date + i, 'D', 'NLS_DATE_LANGUAGE=English'),
       TO_CHAR(start_date + i, 'DD'),
       TO_CHAR(start_date + i, 'DDD'),
       TO_CHAR(start_date + i, 'Day', 'NLS_DATE_LANGUAGE=English'),
       TO_CHAR(start_date + i, 'MM'),
       TO_CHAR(start_date + i, 'Month', 'NLS_DATE_LANGUAGE=English'),
       TO_CHAR(start_date + i, 'YYYY'),
       TO_CHAR(start_date + i, 'Q'));
  END LOOP;
END;
/
BEGIN
  pop_date_dim('17102021',3);
END;
/
1 rows affected

SELECT * FROM date_dim


START_DATE
DAYOFWEEK
DAYOFMONTH
DAYOFYEAR
DAYNAME
MONTH
MONTHNAME
YEAR
QUARTER


18-OCT-21
1
18
291
Monday
10
October
2021
4

19-OCT-21
2
19
292
Tuesday
10
October
2021
4

20-OCT-21
3
20
293
Wednesday
10
October
2021
4


--illustration for the first iteration
SELECT SYSDATE + 1 AS start_date,
       TO_CHAR(SYSDATE+ 1, 'D', 'NLS_DATE_LANGUAGE=English') AS dayofweek,
       TO_CHAR(SYSDATE+ 1, 'DD') AS dayofmonth,
       TO_CHAR(SYSDATE+ 1, 'DDD') AS dayofyear,
       TO_CHAR(SYSDATE+ 1, 'Day', 'NLS_DATE_LANGUAGE=English') AS dayname,
       TO_CHAR(SYSDATE+ 1, 'MM') AS month,
       TO_CHAR(SYSDATE+ 1, 'Month', 'NLS_DATE_LANGUAGE=English') AS monthname,
       TO_CHAR(SYSDATE+ 1, 'YYYY') AS year,
       TO_CHAR(SYSDATE+ 1, 'Q') AS quarter
  FROM dual


START_DATE
DAYOFWEEK
DAYOFMONTH
DAYOFYEAR
DAYNAME
MONTH
MONTHNAME
YEAR
QUARTER


21-APR-22
4
21
111
Thursday
04
April
2022
2

START_DATE	DAYOFWEEK	DAYOFMONTH	DAYOFYEAR	DAYNAME	MONTH	MONTHNAME	YEAR	QUARTER
18-OCT-21	1	18	291	Monday	10	October	2021	4
19-OCT-21	2	19	292	Tuesday	10	October	2021	4
20-OCT-21	3	20	293	Wednesday	10	October	2021	4