I am trying to create a procedure so the dates can be easily passed instead of modifying working code.
<html>
INSERT INTO timeoff
(employee_id, timeoff_date)
SELECT e.employee_id,
c.date_val
FROM employees e
INNER JOIN table(generate_dates_pipelined(date '2021-08-01', DATE '2021-08-10')) c
PARTITION BY ( e.employee_id )
ON (SUBSTR(e.work_days, TRUNC(c.date_val) - TRUNC(c.date_val, 'IW') + 1, 1) = 'Y')
WHERE NOT EXISTS (
SELECT 1
FROM holidays h
WHERE c.date_val = h.holiday_date
)
ORDER BY
e.employee_id,
c.date_val
;
</html>
Below are two different versions of the procedure date_test(), which are partially coded to remove holidays, which is the first step I'm working on.
In the first procedure everything seems to work fine. In the second procedure, which emulates my SQL, the one I want to replace the SQL with, I get errors when I try to create the procedure.
Can someone familiar with PLSQL tell me what's wrong with the second procedure as I can't seem to figure out the problem. I want the second procedure as close as possible to my working SQL and would like to understand what my problem is and how to fix it.
The error when I try to create the second procedure is as follows:
Errors: PROCEDURE DATE_TEST
Line/Col: 7/7 PL/SQL: SQL Statement ignored
Line/Col: 7/14 PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got DATE
Below is my test CASE. I'm testing in live sql so we can both have the same Oracle version. Thanks in advance to all that answer.
<html>
CREATE OR REPLACE TYPE obj_date IS OBJECT (
date_val DATE
);
CREATE OR REPLACE TYPE nt_date IS TABLE OF obj_date;
create or replace function generate_dates_pipelined(
p_from in date,
p_to in date
)
return nt_date
pipelined
is
begin
for c1 in (
with calendar (start_date, end_date ) as (
select trunc(p_from), trunc(p_to) from dual
union all
select start_date + 1, end_date
from calendar
where start_date + 1 <= end_date
)
select start_date as day
from calendar
) loop
pipe row (obj_date(c1.day));
end loop;
return;
end generate_dates_pipelined;
create table holidays(
holiday_date DATE not null,
holiday_name VARCHAR2(20),
constraint holidays_pk primary key (holiday_date),
constraint is_midnight check ( holiday_date = trunc ( holiday_date ) )
);
INSERT into holidays (HOLIDAY_DATE,HOLIDAY_NAME) WITH dts as (
select to_date('01-AUG-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'August 1st 2021' from dual union all
select to_date('05-AUG-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'August 5th 2021' from dual
)
SELECT * from dts;
Create table employees(
employee\_id NUMBER(6),
first\_name VARCHAR2(20),
last\_name VARCHAR2(20),
card\_num VARCHAR2(10),
work\_days VARCHAR2(7)
);
ALTER TABLE employees
ADD ( CONSTRAINT employees\_pk
PRIMARY KEY (employee\_id));
INSERT INTO employees
(
EMPLOYEE_ID,
first_name,
last_name,
card_num,
work_days
)
WITH names AS (
SELECT 1, 'Jane', 'Doe', 'F123456', 'NYYYYYN' FROM dual UNION ALL
SELECT 2, 'Madison', 'Smith', 'R33432','NYYYYYN'
FROM dual UNION ALL
SELECT 3, 'Justin', 'Case', 'C765341','NYYYYYN'
FROM dual UNION ALL
SELECT 4, 'Mike', 'Jones', 'D564311','NYYYYYN' FROM dual
) SELECT * FROM names;
create table timeoff(
seq_num integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
employee_id NUMBER(6),
timeoff_date DATE,
timeoff_type VARCHAR2(1) DEFAULT 'V',
constraint timeoff_chk check (timeoff_date=trunc(timeoff_date, 'dd')),
constraint timeoff_pk primary key (employee_id, timeoff_date)
);
###### Works fine ########
CREATE OR REPLACE PROCEDURE date_test (start_date DATE, end_date DATE)
IS
l_res nt_date;
i NUMBER;
l_cnt NUMBER;
BEGIN
SELECT generate_dates_pipelined (start_date, end_date)
INTO l_res
FROM DUAL;
DBMS\_OUTPUT.put\_line ('contents of L\_RES (all dates) ------------');
FOR i IN l\_res.FIRST .. l\_res.LAST
LOOP
DBMS\_OUTPUT.put\_line (l\_res (i).date\_val);
END LOOP;
DBMS\_OUTPUT.put\_line ('removing holidays -------------------------');
FOR i IN l\_res.FIRST .. l\_res.LAST
LOOP
SELECT MAX (1)
INTO l\_cnt
FROM holidays
WHERE holiday\_date = l\_res (i).date\_val;
DBMS\_OUTPUT.put\_line (
l\_res (i).date\_val || ': cnt = ' || l\_cnt || ' - delete it!');
IF l\_cnt = 1
THEN
l\_res.delete (i);
END IF;
END LOOP;
DBMS\_OUTPUT.put\_line ('contents of L\_RES (holidays excluded) ----');
i := l\_res.FIRST;
WHILE i IS NOT NULL
LOOP
DBMS\_OUTPUT.put\_line (l\_res (i).date\_val);
i := l\_res.NEXT (i);
END LOOP;
END;
EXEC date\_test(DATE '2021-08-01', DATE '2021-08-10');
#### doesn't create ###
CREATE OR REPLACE PROCEDURE date_test (start_date DATE, end_date DATE)
IS
l_res nt_date;
BEGIN
SELECT date\_val INTO l\_res
FROM TABLE (
generate\_dates\_pipelined (start\_date, end\_date))
WHERE NOT EXISTS
(SELECT 1
FROM holidays h
WHERE date\_val = h.holiday\_date);
END;
EXEC date\_test(DATE '2021-08-01', DATE '2021-08-10');
</html>