PLSQL problem creating procedure inconsistent data types

BeefStu · Jul 26, 2021 1:59PM

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>

James Su · Jul 26, 2021 4:27PM

The pipelined function returns more than one rows so you can't use select ... into.

If you need to keep those returned dates, define a date collection and use select ... bulk collect into ...

Paulzip · Jul 26, 2021 2:24PM

date_val is a date

l_res is an object with an attribute of date_val

Hence why they aren't compatible. Try this...

   SELECT r.column_value INTO l_res
         FROM TABLE (
           generate_dates_pipelined (start_date, end_date)) r
     WHERE NOT EXISTS
             (SELECT 1
                 FROM holidays h
                WHERE date_val = h.holiday_date);

or

       l_res  date;
    BEGIN
      SELECT r.date_val INTO  l_res
         FROM TABLE (
           generate_dates_pipelined (start_date, end_date)) r
     WHERE NOT EXISTS
[snip...=

BeefStu · Jul 26, 2021 2:53PM

@Paulzip I tried the second option and it created. When I tried to run it I got the following error. Note I defined 'r' as an alias to the function call.

The error when I tried to run.

ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SQL_DUVMRNJCZKCNTKYBNWXVCHSMA.DATE_TEST", line 7

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_SQL", line 1721

Any other suggestions? I appreciate your response

Frank Kulash · Jul 26, 2021 3:02PM

Hi, @BeefStu

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

So the error message points to line 7 of the procedure. Here's the code, starting with line 7:

SELECT date_val INTO l_res

FROM TABLE (

generate_dates_pipelined (start_date, end_date))

Before you invest any more time into this, try to find a simpler approach. Whatever you're trying to do, it can probably be done without user-defined types.

Frank Kulash · Jul 26, 2021 3:10PM

Hi, @BeefStu

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 ) )

);

Not that you asked: I recommend a calendar table that includes all dates, with weekends and holidays marked. It will be helpful for a lot of problems, and very efficient. See:

PL/SQL function to calculate non-working days — oracle-tech

EdStevens · Jul 26, 2021 3:28PM

Your postings would be much easier to read if you would format the code.

https://community.oracle.com/tech/apps-infra/kb/articles/12-format-text

BeefStu · Jul 26, 2021 3:32PM

@Frank Kulash I stumbled upon that yesterday. The problem I have is that some employees are scheduled to work on SAT/SUN while off on other days. If you recall last week you asked me about the 7 byte work_days string in my employees table that has either a Y/N. Much of my code depends on it.

I think getting back all dates between a from and to date will fit my requirement better and I can discard what I don't need. I'm doing that already in SQL, which is at the top of my post. Thanks for the suggestions there very much welcome

BeefStu · Jul 26, 2021 3:42PM

@EdStevens so all code should go between </>. Not a problem I will remember that for future posts. Thanks for the link

BeefStu · Jul 26, 2021 4:15PM

@Frank Kulash

Johnathan Lewis

wrote the following based on some of my code so I am trying to call his function

https://jonathanlewis.wordpress.com/all-postings/

James Su · Jul 26, 2021 4:27PM

The pipelined function returns more than one rows so you can't use select ... into.

If you need to keep those returned dates, define a date collection and use select ... bulk collect into ...

BeefStu · Jul 26, 2021 4:59PM

@James Su can you please provide a working example based on my code as I am a DBA and not a developer.

I want to collect all the dates from the PIPE function then remove the holidays with a NOT EXISTS command.

Thanks in advance for your patience and any help

Forum Stats

Quick Links

Categories

PLSQL problem creating procedure inconsistent data types

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

PLSQL problem creating procedure inconsistent data types

Best Answer

Answers