PL/SQL loop over table type and extend

Question

I'm currently learning PL/SQL and my task is to create a package which finds all German holidays and inserts them into one collection. My problem is that I have to insert them by looping over them and by extending the table. It's easy to extend the table manually but way too much work and my teacher won't allow the manual extends. I appreciate any solution or help.

    CREATE OR REPLACE PACKAGE BODY pa_feiertage_mau IS


  FUNCTION calc_holidays (in_year_c IN INTEGER)
    RETURN DATE
  IS
     v_k     INTEGER;
     v_m     INTEGER;
     v_s     INTEGER;
     v_a     INTEGER;
     v_d     INTEGER;
     v_r     INTEGER;
     v_og    INTEGER;
     v_sz    INTEGER;
     v_oe    INTEGER;
     v_os    INTEGER;
     v_day   INTEGER;
     v_month INTEGER;
  BEGIN
   v_k := floor(in_year_c / 100);
   v_m := 15 + floor((3 * v_k + 3) / 4) - floor((8 * v_k + 13) / 25);
   v_s := 2 - floor((3 * v_k + 3) / 4);
   v_a := MOD(in_year_c, 19);
   v_d := MOD((19 * v_a + v_m), 30);
   v_r := floor(v_d / 29) + (floor(v_d / 28) - floor(v_d / 29)) * floor(v_a / 11);
   v_og := 21 + v_d - v_r;
   v_sz := 7 - MOD((in_year_c + floor(in_year_c / 4) + v_s), 7);
   v_oe := 7 - MOD(v_og - v_sz, 7);
   v_os := v_og + v_oe;
   IF (v_os <= 31) THEN
    v_day := v_os;
    v_month := 3;
   ELSE
    v_day := v_os - 31;
    v_month := 4;
   END IF;
  RETURN TO_DATE(v_day || '.' || v_month || '.' || in_year_c, 'DD,MM,YYYY');

  END calc_holidays;


  FUNCTION get_holidays (in_year1 IN VARCHAR2)
    RETURN Type_tab_feiertage
    PIPELINED
  IS
    /*Static holidays */

    v_year1   VARCHAR2(4)   := in_year1;
    v_neujahr VARCHAR2(10)  := '01.01.';
    v_3kings  VARCHAR2(10)  := '06.01.';
    v_fraut   VARCHAR2(10)  := '08.03.';
    v_arbeit  VARCHAR2(10)  := '01.05.';
    v_himmel  VARCHAR2(10)  := '15.08.';
    v_deu     VARCHAR2(10)  := '03.10.';
    v_refo    VARCHAR2(10)  := '31.10.';
    v_aller   VARCHAR2(10)  := '01.11.';
    v_wh1     VARCHAR2(10)  := '25.12.';
    v_wh2     VARCHAR2(10)  := '26.12.';

    /*Changing holidays*/

    d_ostern   DATE         := calc_holidays(TO_NUMBER(in_year1));
    d_karf     DATE         := d_ostern   - INTERVAL '2'  DAY;
    d_gruen    DATE         := d_ostern   - INTERVAL '3'  DAY;
    d_osterm   DATE         := d_ostern   + INTERVAL '1'  DAY;
    d_chimmel  DATE         := d_ostern   + INTERVAL '39' DAY;
    d_pfingsts DATE         := d_ostern   + INTERVAL '49' DAY;
    d_pfingstm DATE         := d_pfingsts + INTERVAL '1'  DAY;
    d_fronlei  DATE         := d_ostern   + INTERVAL '60' DAY;
    
    tab_feiertage     type_tab_feiertage;
    
  BEGIN
    
    tab_feiertage   := NEW type_tab_feiertage();

    LOOP

      tab_feiertage.EXTEND();
      tab_feiertage( tab_feiertage.LAST).DATUM    := TO_DATE( v_neujahr || v_year1, 'DD.MM.YYYY');
      tab_feiertage( tab_feiertage.LAST).FEIERTAG := 'Neujahr';
      

    END LOOP;

    IF tab_feiertage.COUNT > 0 THEN
      FOR i IN tab_feiertage.FIRST .. tab_feiertage.LAST
      LOOP
        PIPE ROW( tab_feiertage( i));
      END LOOP;
      --RETURN tab_feiertage;
    END IF;

    RETURN;

  END get_holidays;  
END pa_feiertage_mau;

Answer 1

To be honest it's hard to tell what your teacher is driving at. You have some fixed reference data keys (holiday names) and logic for deriving their reference values, and you need to put the keys and derived values in a collection. There is no magic way of doing this: each entry needs to be assigned manually.

Personally I would discard the local variables (v_%, d_%) and put that logic and the names in the collection populating code. The only variable I would keep is d_ostern, because you need that to anchor all the other moveable feasts. Note the change to how Pfingstm is derived.

d_ostern := calc_holidays(TO_NUMBER(in_year1));

tab_feiertage.EXTEND();
tab_feiertage( tab_feiertage.LAST).DATUM    := TO_DATE( '01.01.' || v_year1, 'DD.MM.YYYY');
tab_feiertage( tab_feiertage.LAST).FEIERTAG := 'Neujahr';
...
tab_feiertage.EXTEND();
tab_feiertage( tab_feiertage.LAST).DATUM    := d_ostern;
tab_feiertage( tab_feiertage.LAST).FEIERTAG := 'Ostern';
tab_feiertage.EXTEND();
tab_feiertage( tab_feiertage.LAST).DATUM    := d_ostern - INTERVAL '2'  DAY;
tab_feiertage( tab_feiertage.LAST).FEIERTAG := 'Karf';
...
tab_feiertage.EXTEND();
tab_feiertage( tab_feiertage.LAST).DATUM    := d_ostern + INTERVAL '50'  DAY;
tab_feiertage( tab_feiertage.LAST).FEIERTAG := 'Pfingstm');
...

---

There is a way of populating the collection without using manual extension and explicit assignment, but it requires SQL. Here we calculate the value of Ostern using a WITH clause to call the calc_holidays() function, then write a massive UNION ALL query which selects the key-value pairs and stores them in the collection using BULK COLLECT:

select datum, feiertag
bulk collect into tab_feiertage
from (
    /* calculate moveable feast */
    with hol as ( 
      select calc_holidays(TO_NUMBER(in_year1)) as ostern
      from   dual ) 

    /* Static holidays */
    select to_date('01.01.'||in_year1, 'DD.MM.YYYY') as datum, 'Neujahr'   as feiertag from hol union all
    select to_date('06.01.'||in_year1, 'DD.MM.YYYY') as datum, '3kings'    as feiertag from hol union all
    select to_date('08.03.'||in_year1, 'DD.MM.YYYY') as datum, 'Fraut'     as feiertag from hol union all
    select to_date('01.05.'||in_year1, 'DD.MM.YYYY') as datum, 'Arbeit'    as feiertag from hol union all
    select to_date('15.08.'||in_year1, 'DD.MM.YYYY') as datum, 'Himmel'    as feiertag from hol union all
    select to_date('03.10.'||in_year1, 'DD.MM.YYYY') as datum, 'Deu'       as feiertag from hol union all
    select to_date('31.10.'||in_year1, 'DD.MM.YYYY') as datum, 'Refo'      as feiertag from hol union all
    select to_date('01.11.'||in_year1, 'DD.MM.YYYY') as datum, 'Aller'     as feiertag from hol union all
    select to_date('25.12.'||in_year1, 'DD.MM.YYYY') as datum, 'Wh1'       as feiertag from hol union all
    select to_date('26.12.'||in_year1, 'DD.MM.YYYY') as datum, 'Wh2'       as feiertag from hol union all
    
    /* Changing holidays */
    select ostern                                             , 'Ostern'   as feiertag from hol union all
    select ostern - INTERVAL  '2' DAY as datum                , 'Karf'     as feiertag from hol union all
    select ostern - INTERVAL  '3' DAY as datum                , 'Gruen'    as feiertag from hol union all
    select ostern + INTERVAL  '1' DAY as datum                , 'Osterm'   as feiertag from hol union all
    select ostern + INTERVAL '39' DAY as datum                , 'Chimmel'  as feiertag from hol union all
    select ostern + INTERVAL '49' DAY as datum                , 'Pfingsts' as feiertag from hol union all
    select ostern + INTERVAL '50' DAY as datum                , 'Pfingstm' as feiertag from hol union all
    select ostern + INTERVAL '60' DAY as datum                , 'Fronlei'  as feiertag from hol
);

This may not be what your teacher expects but it works. There is a demo on db<>fiddle here