How do I create data (not a temp table) using a loop and Oracle 12c?

Question

I'm using a PaaS (Asset Works) to create a report that needs looping. I have a bus table and need to create a spreadsheet that lists the bus replacements. Some buses need to be replaced every 10 years, others every 12 years. My report needs to list all the replacements over the next 50 years.

I've found the looping structure for Counter in 1..MaxCounter loop and see how to execute a query and put the values INTO another table. The problem is this isn't my Oracle db. I can't create a table, temp or perm. So my query needs to hold the values for use in my report.

Any suggestions?

Thanks in advance! Bruce

Declare
Counter number;
MaxCounter number := 50;
StartYear number := 2021;

BEGIN

for Counter in 1..MaxCounter loop
    Select * From eq_main where year(Delivery_Date) = startyear + maxcounter;
    -- Save this rowset somewhere...

end Loop;
commit;
end;

Answer 1

If I got it right you need the data for 50 years in advance about bus replacements per year and bus group - according to validity periods in years per bus group.
Some dummy sample data to work with:

WITH    --  S a m p l e   D a t a :
    tbl (ID, DELIVERY_DATE, BUS_GROUP, YEARS_VALID, AMOUNT) AS
        (   Select 1, DATE '2021-01-01', 'B', 10, 68000 From Dual Union All
            Select 2, DATE '2021-02-01', 'C', 12, 97000 From Dual Union All
            Select 3, DATE '2021-03-02', 'C', 12, 97000 From Dual Union All
            Select 4, DATE '2021-04-01', 'B', 10, 68000 From Dual Union All
            Select 5, DATE '2021-05-01', 'C', 12, 97000 From Dual Union All
            Select 6, DATE '2022-02-01', 'B', 10, 68000 From Dual Union All
            Select 7, DATE '2022-03-02', 'A',  5, 43000 From Dual Union All 
            Select 8, DATE '2022-09-02', 'A',  5, 43000 From Dual Union All
            Select 9, DATE '2023-01-02', 'B', 10, 68000 From Dual  
        ), 

Create 2 CTEs - one for list of next 50 years and another ( joining your data to the first ) with the years when any particular group period requires the replacement :

  years_50 AS
    (   Select To_Number(To_Char(SYSDATE, 'yyyy')) + (LEVEL - 1) "A_YEAR"
        From Dual
        Connect By LEVEL <= 50
    ),
  replacements AS
    ( Select      y.A_YEAR,
                  t.BUS_GROUP, t.AMOUNT
      From        years_50 y
      Inner Join  tbl t ON( y.A_YEAR >= To_Number(To_Char(t.DELIVERY_DATE, 'yyyy')) + YEARS_VALID And 
                            Mod((y.A_YEAR - To_Number(To_Char(t.DELIVERY_DATE, 'yyyy'))), YEARS_VALID) = 0)
    )

Now we can group, count and calculate totals of the data from replacements cte to get how many busses per group should be replaced:

Option 1.

--  M a i n   S Q L :
SELECT    A_YEAR, BUS_GROUP, 
          Count(BUS_GROUP) "QUANTITY", 
          AMOUNT "PRICE", 
          Count(BUS_GROUP) * AMOUNT "TOTAL"
FROM      replacements
GROUP BY  A_YEAR, BUS_GROUP, AMOUNT
ORDER BY  A_YEAR, BUS_GROUP

/*  R e s u l t : 
    A_YEAR BUS_GROUP   QUANTITY      PRICE      TOTAL
---------- --------- ---------- ---------- ----------
      2027 A                  2      43000      86000
      2031 B                  2      68000     136000
      2032 A                  2      43000      86000
      2032 B                  1      68000      68000
      2033 B                  1      68000      68000
      2033 C                  3      97000     291000
      2037 A                  2      43000      86000
      2041 B                  2      68000     136000
      2042 A                  2      43000      86000
      2042 B                  1      68000      68000
      2043 B                  1      68000      68000
      2045 C                  3      97000     291000
      2047 A                  2      43000      86000
      2051 B                  2      68000     136000
      2052 A                  2      43000      86000
      2052 B                  1      68000      68000
      2053 B                  1      68000      68000
      2057 A                  2      43000      86000
      2057 C                  3      97000     291000
      2061 B                  2      68000     136000
      2062 A                  2      43000      86000
      2062 B                  1      68000      68000
      2063 B                  1      68000      68000
      2067 A                  2      43000      86000
      2069 C                  3      97000     291000
      2071 B                  2      68000     136000
      2072 A                  2      43000      86000
      2072 B                  1      68000      68000 */

If you want all years to be listed, even if there are no replacements, then just change Inner Join in replacements CTE with Left Join.

Option 2. - PIVOT

--  M a i n   S Q L :
SELECT *
FROM    ( SELECT    A_YEAR, BUS_GROUP, 
                    Count(BUS_GROUP) "QUANTITY", 
                    AMOUNT "PRICE", 
                    Count(BUS_GROUP) * AMOUNT "TOTAL"
          FROM      replacements
          GROUP BY  A_YEAR, BUS_GROUP, AMOUNT
        )
PIVOT   ( Max(QUANTITY) "QUANTITY", Max(TOTAL) "TOTAL" For BUS_GROUP IN('A' "GROUP_A", 'B' "GROUP_B", 'C' "GROUP_C") )
ORDER BY  A_YEAR

/*
    A_YEAR      PRICE GROUP_A_QUANTITY GROUP_A_TOTAL GROUP_B_QUANTITY GROUP_B_TOTAL GROUP_C_QUANTITY GROUP_C_TOTAL
---------- ---------- ---------------- ------------- ---------------- ------------- ---------------- -------------
      2027      43000                2         86000                                                              
      2031      68000                                               2        136000                               
      2032      43000                2         86000                                                              
      2032      68000                                               1         68000                               
      2033      68000                                               1         68000                               
      2033      97000                                                                              3        291000
      2037      43000                2         86000                                                              
      2041      68000                                               2        136000                               
      2042      43000                2         86000                                                              
      2042      68000                                               1         68000                               
      2043      68000                                               1         68000                               
      2045      97000                                                                              3        291000
      2047      43000                2         86000                                                              
      2051      68000                                               2        136000                               
      2052      43000                2         86000                                                              
      2052      68000                                               1         68000                               
      2053      68000                                               1         68000                               
      2057      43000                2         86000                                                              
      2057      97000                                                                              3        291000
      2061      68000                                               2        136000                               
      2062      43000                2         86000                                                              
      2062      68000                                               1         68000                               
      2063      68000                                               1         68000                               
      2067      43000                2         86000                                                              
      2069      97000                                                                              3        291000
      2071      68000                                               2        136000                               
      2072      43000                2         86000                                                              
      2072      68000                                               1         68000                               */