By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 |
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | 0 |
CREATE OR REPLACE PACKAGE pa_feiertage_mau IS
type type_feiertage is record (
datum date
, feiertag varchar2(30)
);
type type_tab_feiertage is table of type_feiertage;
FUNCTION calc_holidays (in_year_c IN INTEGER)
RETURN DATE;
FUNCTION get_holidays (in_year1 IN VARCHAR2)
RETURN Type_tab_feiertage
PIPELINED;
end;
/
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;
select * from all_errors where name = 'PA_FEIERTAGE_MAU';
select * from table(pa_feiertage_mau.get_holidays ('2020'))t order by t.datum ;
| DATUM | FEIERTAG |
|---|---|
| 01-JAN-20 | Neujahr |
| 06-JAN-20 | 3kings |
| 08-MAR-20 | Fraut |
| 09-APR-20 | Gruen |
| 10-APR-20 | Karf |
| 12-APR-20 | Ostern |
| 13-APR-20 | Osterm |
| 01-MAY-20 | Arbeit |
| 21-MAY-20 | Chimmel |
| 31-MAY-20 | Pfingsts |
| 01-JUN-20 | Pfingstm |
| 11-JUN-20 | Fronlei |
| 15-AUG-20 | Himmel |
| 03-OCT-20 | Deu |
| 31-OCT-20 | Refo |
| 01-NOV-20 | Aller |
| 25-DEC-20 | Wh1 |
| 26-DEC-20 | Wh2 |