Oracle: Generate rows for missing years within range (sysyear + 9)

I have an Oracle 18c table that has rows for certain years:

with data (year_, amount) as (
select 2024, 100 from dual union all
select 2025, 200 from dual union all
select 2025, 300 from dual union all
select 2026, 400 from dual union all
select 2027, 500 from dual union all
select 2028, 600 from dual union all
select 2028, 700 from dual union all
select 2028, 800 from dual union all
select 2029, 900 from dual union all
select 2031, 100 from dual
)
select
    *
from
    data

     YEAR_     AMOUNT
---------- ----------
      2024        100
      2025        200
      2025        300
      2026        400
      2027        500
      2028        600
      2028        700
      2028        800
      2029        900
      2031        100

I want at least one row for each year within this range: sysyear + 9. In other words, I want rows for 10 years, starting with the current year (currently 2023).

I'm missing rows for certain years: 2023, 2030, and 2032. So I want to generate filler rows for those missing years. The amount for the filler rows would be null.

It would look like this:

     YEAR_     AMOUNT
---------- ----------
      2023             --filler
      2024        100
      2025        200
      2025        300
      2026        400
      2027        500
      2028        600
      2028        700
      2028        800
      2029        900
      2030             --filler
      2031        100
      2032             --filler

In an Oracle SQL query, how can I select the rows and generate filler rows within the 10 year range?

Edit: I would prefer not to manually create a list of years in the query or in a table. I would rather create a dynamic range within the query.

Solution

Try it like here:

Select    y.YEAR_, t.AMOUNT
From      (Select EXTRACT(YEAR From SYSDATE) + LEVEL - 1 "YEAR_" From Dual Connect By LEVEL <= 10) y
Left Join tbl t ON(t.YEAR_ = y.YEAR_)
Order By  y.YEAR_, t.AMOUNT

With your sample data:

WITH 
    tbl (YEAR_, AMOUNT) AS 
        (
            Select 2024, 100 From Dual Union All
            Select 2025, 200 From Dual Union All
            Select 2025, 300 From Dual Union All
            Select 2026, 400 From Dual Union All
            Select 2027, 500 From Dual Union All
            Select 2028, 600 From Dual Union All
            Select 2028, 700 From Dual Union All
            Select 2028, 800 From Dual Union All
            Select 2029, 900 From Dual Union All
            Select 2031, 100 From Dual
        )

... the result is:

YEAR_	AMOUNT
2023
2024	100
2025	200
2025	300
2026	400
2027	500
2028	600
2028	700
2028	800
2029	900
2030
2031	100
2032