Forum Stats

  • 3,781,497 Users
  • 2,254,524 Discussions
  • 7,879,733 Comments

Discussions

Categories

PLSQL problem creating procedure inconsistent data types

BeefStu
BeefStu Member Posts: 300 Blue Ribbon
edited Jul 26, 2021 9:29PM in SQL & PL/SQL


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>

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • James Su
    James Su Member Posts: 1,114 Gold Trophy
    Accepted Answer

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

    · Share on TwitterShare on Facebook
«1

Answers

  • Paulzip
    Paulzip Member Posts: 8,539 Blue Diamond
    edited Jul 26, 2021 2:27PM

    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...=
    · Share on TwitterShare on Facebook
  • BeefStu
    BeefStu Member Posts: 300 Blue Ribbon

    @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

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,381 Red Diamond

    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.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,381 Red Diamond

    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

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,604 Gold Crown

    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
    · Share on TwitterShare on Facebook
  • BeefStu
    BeefStu Member Posts: 300 Blue Ribbon

    @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

    · Share on TwitterShare on Facebook
  • BeefStu
    BeefStu Member Posts: 300 Blue Ribbon

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

    · Share on TwitterShare on Facebook
  • BeefStu
    BeefStu Member Posts: 300 Blue Ribbon

    @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/

    · Share on TwitterShare on Facebook
  • James Su
    James Su Member Posts: 1,114 Gold Trophy
    Accepted Answer

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

    · Share on TwitterShare on Facebook
  • BeefStu
    BeefStu Member Posts: 300 Blue Ribbon

    @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

    · Share on TwitterShare on Facebook