PL/SQL get the real number of weeks in a year in next 6 months

Here is the original query modified to only show 6 months of data for the next 6 months, starting at the first day of the current month:

SELECT LISTAGG (
                         DISTINCT TO_CHAR (TRUNC (mydate, 'MONTH'), 'MONTH'),
                         ', ')                              AS MONTHS,
                     TO_CHAR (nr_of_sundays + 1, 'fm09')    week_num
                FROM (SELECT mydate,
                               (  (  TRUNC (mydate, 'day')
                                   - TRUNC (TRUNC (mydate, 'yyyy'), 'day'))
                                / 7)
                             + CASE
                                   WHEN TO_CHAR (TRUNC (mydate, 'YYYY'), 'day') =
                                        'sun'
                                   THEN
                                       1
                                   ELSE
                                       0
                               END    AS nr_of_sundays
                        FROM (    SELECT TRUNC (SYSDATE, 'mm') - 1 + LEVEL    AS mydate --
                                    FROM DUAL
                              CONNECT BY LEVEL <=
                                           TRUNC (ADD_MONTHS (SYSDATE, 6), --
                                                  'mm') --
                                         - TRUNC (SYSDATE, 'mm'))) --
               WHERE TO_DATE (mydate, 'DD/MM/YY') >=
                     TO_DATE (SYSDATE, 'DD/MM/YY')
            GROUP BY TO_CHAR (nr_of_sundays + 1, 'fm09')
            ORDER BY TO_CHAR (nr_of_sundays + 1, 'fm09');
            
            SELECT TRUNC (SYSDATE, 'mm') - 1 + LEVEL    AS mydate 
                                    FROM DUAL
                              CONNECT BY LEVEL <=
                                           TRUNC (ADD_MONTHS (SYSDATE, 6), 
                                                  'mm') 
                                         - TRUNC (SYSDATE, 'mm');

MONTHS      WEEK_NUM

JANUARY     01
JANUARY     02
JANUARY     03
JANUARY     04
FEBRUARY , JANUARY      05
FEBRUARY    06
FEBRUARY    07
FEBRUARY    08
FEBRUARY    09
SEPTEMBER   38
SEPTEMBER   39
OCTOBER  , SEPTEMBER    40
...

However, because the results only have columns "months" and "week_num" and the order by is by week_num - it will look odd. The first row will be the first week_num of next year. You could add the year to each row. Here is a rewritten query using exactly the same logic but including the year for each row:

WITH date_range (mydate) AS
(
   SELECT TRUNC (SYSDATE, 'mm') - 1 + LEVEL
     FROM DUAL
         CONNECT BY LEVEL <=  TRUNC (ADD_MONTHS (SYSDATE, 6),'mm') - TRUNC (SYSDATE, 'mm')
), weeknum_per_date (mydate, nr_of_sundays) AS
(
   SELECT mydate,
          (TRUNC (mydate, 'day') - TRUNC (TRUNC (mydate, 'yyyy'), 'day')) / 7
          + CASE WHEN TO_CHAR (TRUNC (mydate, 'YYYY'), 'day') = 'sun' THEN 1 ELSE 0 END    AS nr_of_sundays 
     FROM date_range
)
SELECT TO_CHAR(mydate, 'YYYY') as year, LISTAGG (DISTINCT TO_CHAR (TRUNC (mydate, 'MONTH'), 'MONTH'),
                         ', ')                              AS MONTHS,
                     TO_CHAR (nr_of_sundays + 1, 'fm00')    week_num
  FROM weeknum_per_date                                            
               WHERE TO_DATE (mydate, 'DD/MM/YY') >=
                     TO_DATE (SYSDATE, 'DD/MM/YY')
            GROUP BY TO_CHAR (nr_of_sundays + 1, 'fm00'), TO_CHAR(mydate, 'YYYY')
            ORDER BY TO_CHAR(mydate, 'YYYY'), TO_CHAR (nr_of_sundays + 1, 'fm00'); 

year    month      week_num
2022    SEPTEMBER   38
2022    SEPTEMBER   39
2022    OCTOBER  , SEPTEMBER    40
2022    OCTOBER     41
2022    OCTOBER     42
2022    OCTOBER     43
2022    OCTOBER     44
2022    NOVEMBER , OCTOBER      45
2022    NOVEMBER    46
2022    NOVEMBER    47
2022    NOVEMBER    48
2022    DECEMBER , NOVEMBER     49
2022    DECEMBER    50
2022    DECEMBER    51
2022    DECEMBER    52
2022    DECEMBER    53
2023    JANUARY     01
2023    JANUARY     02
...

If you want the ISO week (which starts on a Monday and ends on a Sunday and belongs to whichever year contains the majority of its days) then:

SELECT CASE
       WHEN EXTRACT(MONTH FROM week_start)
            = EXTRACT(MONTH FROM week_end)
       THEN TO_CHAR(week_start, 'fmMONTH')
       ELSE TO_CHAR(week_start, 'fmMONTH')
            || ', ' || TO_CHAR(week_end, 'fmMONTH')
       END as month,
       TO_CHAR(week_start, 'IW') AS iso_week,
       TO_CHAR(week_start, 'IYYY') AS iso_year
FROM   (
  SELECT TRUNC(ADD_MONTHS(SYSDATE, -12), 'IW') + (LEVEL - 1) * 7 AS week_start,
         TRUNC(ADD_MONTHS(SYSDATE, -12), 'IW') + LEVEL * 7 - 1 AS week_end
  FROM   DUAL
  CONNECT BY TRUNC(ADD_MONTHS(SYSDATE, -12), 'IW') + (LEVEL - 1) * 7 <= TRUNC(SYSDATE)
);

Which outputs:

fiddle