Forum Stats

  • 3,751,645 Users
  • 2,250,380 Discussions
  • 7,867,492 Comments

Discussions

Oracle returning a cursor from a function or procedure

BeefStu
BeefStu Member Posts: 278 Blue Ribbon

I have the following code, which appears to be working fine.


As you can in the last piece of SQL the date range is hard coded. Is there a way to wrap it in a procedure or function and return a cursor (passing the dates directly to the procedure or function ) then modifying my SQL to reference each day  that falls between the date range.


Ie  something like this without the hard coded dates



WITH calendar ( start_date, end_date ) AS (

  SELECT DATE '2021-07-01', DATE '2021-07-30' 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



    CREATE OR REPLACE PROCEDURE generate_dates

     (

      p_start_date  IN DATE,

     p_end_date   IN DATE

    )

    AS 

        BEGIN

        END;

    END generate_dates;



EXEC generate_dates(

DATE '2021-07-01',

DATE '2021-07-31');



ALTER SESSION SET 

       NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';



    create table holidays(

      holiday_date DATE,

     holiday_name VARCHAR2(20)

    );


    INSERT into holidays

      (holiday_date,

       holiday_name)

     VALUES

    (

     TO_DATE('2021/07/21 00:00:00',   'yyyy/mm/dd hh24:mi:ss'), 'July 21 2021');


   

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

     constraint timeoff_chk check (timeoff_date=trunc(timeoff_date, 'dd')),

      constraint timeoff_pk primary key (employee_id, timeoff_date)

     );



   INSERT INTO timeoff (EMPLOYEE_ID,TIMEOFF_DATE,TIMEOFF_TYPE

    ) 

     WITH dts AS ( 

        SELECT 1, to_date('20210726 00:00:00','YYYYMMDD HH24:MI:SS'),'V'    FROM dual UNION ALL 

    SELECT 2, to_date('20210726 00:00:00','YYYYMMDD HH24:MI:SS'),'V'    FROM dual UNION ALL 

    SELECT 2, to_date('20210727 00:00:00','YYYYMMDD HH24:MI:SS'),'V'    FROM dual  ) 

    SELECT * FROM dts;



            CREATE TABLE  emp_attendance(    

     seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,

      employee_id NUMBER(6),

      start_date DATE,

      end_date DATE,

      week_number NUMBER(2),

      create_date DATE DEFAULT SYSDATE

       );


    create table absences(

    seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,

    employee_id NUMBER(6),

    absent_date DATE,

    constraint absence_chk check (absent_date=trunc(absent_date, 'dd')),

      constraint absence_pk primary key (employee_id, absent_date)

    );



    INSERT INTO emp_attendance (    EMPLOYEE_ID, START_DATE,END_DATE,WEEK_NUMBER)

    WITH dts AS ( 

    SELECT 1, to_date('20210728 13:10:00','YYYYMMDD HH24:MI:SS'),

    to_date('20210728 23:15:00','YYYYMMDD HH24:MI:SS'), 30  FROM dual UNION ALL 

    SELECT 2, to_date('20210728 12:10:10','YYYYMMDD HH24:MI:SS'),

    to_date('20210728 20:15:01','YYYYMMDD HH24:MI:SS'), 30  FROM dual)

   SELECT * FROM dts;



SELECT e.employee_id,

       c.day

FROM   employees e

       INNER JOIN (

         WITH calendar ( start_date, end_date ) AS (

           SELECT DATE '2021-07-01', DATE '2021-07-30' 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

       ) c

       PARTITION BY ( e.employee_id )

       ON (SUBSTR(e.work_days, TRUNC(c.day) - TRUNC(c.day, 'IW') + 1, 1) = 'Y')

WHERE  NOT EXISTS (

         SELECT 1

         FROM   holidays h

         WHERE  c.day = h.holiday_date

       )

AND    NOT EXISTS(

         SELECT 1

         FROM   timeoff t

         WHERE  e.employee_id = t.employee_id

         AND    t.timeoff_date = c.day

       )

ORDER BY

       e.employee_id,

       c.day

«1

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,275 Gold Trophy

    CREATE OR REPLACE PROCEDURE generate_date   (

       p_start_date IN DATE,

       p_end_date  IN DATE,

       p_rc out sys_refcursor

      )

      AS 

    BEGIN

    open p_rc for

    WITH calendar ( start_date, end_date ) AS (

     SELECT p_start_date, p_end_date 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;

    END;


    END generate_dates;

    BeefStu
  • BeefStu
    BeefStu Member Posts: 278 Blue Ribbon

    Thanks for your expertise and help. I have a few questions.

    1) Does the cursor need to be closed when all dates are gathered or does ending the function implicitly close it?

    2) how do I get all the dates available in my SQL. I like to keep the same method and variables or do I need to recode my SQl with a loop, which I prefer to avoid? The code is easy and ledigible this way and I prefer to keep it like that if possible. Can you please provide an example if it isn't too much of a problem.

    Thanks in advance for your help and patience

  • BEDE
    BEDE Oracle Developer Member Posts: 2,275 Gold Trophy

    1) Yes! Aftr you have fetched all the data you need, you must close the cursor. Otherwise, after some time you will get the Oracle error which says there are too many open cursors.

    2) To get all the data from a cursor, be it an explicit cursor or a sys_refcursor, you certainly need a loop in which you fetch.

    The only alternative to that would be a fetch bulk collect, but that may fill up all the available memory.

    So, as for getting all the data from a sys_ref_cursor, you shoudl have something like below.

    ...

    generate_date   (

       v_start_date,

       v_end_date,

       v_rc out);

    loop

    fetch v_rc into v_date;

    exit when v_rc%notfound;

    --- do whatever you need to do with that date

    end loop;

    close v_rc;


    or, for bulk collect:

    type tab_date is table of date;

    tb_date tab_date;

    begin

    ...

    generate_date   (

       v_start_date,

       v_end_date,

       v_rc out);

    fetch v_rc into tb_date;

    close v_rc;

    if tb_date.count>0 then --- some dates were found

    for i in tb_date.first..tb_date.last loop

    --- do whatever you need to do with tb_date(i)

    end loop;

    else

    ---- what to do if no dates were found?

    end if;

  • BluShadow
    BluShadow Member, Moderator Posts: 41,315 Red Diamond


    The only alternative to that would be a fetch bulk collect, but that may fill up all the available memory.


    Not strictly true.

    It could be a pipelined table function which could then be used to query directly from it in another query, and then a cursor for loop in that would implicitly close the cursor upon completion. ;)

  • BeefStu
    BeefStu Member Posts: 278 Blue Ribbon

    I have the following function how can I replace the SQL to call the function (remove generating calendar code).

    Thanks to all that respond

    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(

     p_from IN DATE

    ,p_to IN DATE)

    RETURN nt_date PIPELINED

    IS

     -- normalize inputs to be as-of midnight

     v_from DATE := TRUNC(NVL(p_from, SYSDATE));

     v_to DATE := TRUNC(NVL(p_to, SYSDATE));

    BEGIN

     LOOP

       EXIT WHEN v_from > v_to;

       PIPE ROW (obj_date(v_from));

       v_from := v_from + 1; -- next calendar day

     END LOOP;

     RETURN;

    END generate_dates;

    SELECT e.employee_id,

           c.day

    FROM   employees e

           INNER JOIN (

             WITH calendar ( start_date, end_date ) AS (

               SELECT DATE '2021-07-01', DATE '2021-07-30' 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

           ) c

           PARTITION BY ( e.employee_id )

           ON (SUBSTR(e.work_days, TRUNC(c.day) - TRUNC(c.day, 'IW') + 1, 1) = 'Y')

    WHERE  NOT EXISTS (

             SELECT 1

             FROM   holidays h

             WHERE  c.day = h.holiday_date

           )

    AND    NOT EXISTS(

             SELECT 1

             FROM   timeoff t

             WHERE  e.employee_id = t.employee_id

             AND    t.timeoff_date = c.day

           )

    ORDER BY

           e.employee_id,

           c.day

  • BluShadow
    BluShadow Member, Moderator Posts: 41,315 Red Diamond

    I don't have time at the minute to do a specific example of the pipeline table function against your data/query... but here's an example from my library which should be enough to show how to do it...

    SQL> CREATE OR REPLACE TYPE myemp AS OBJECT
      2  ( empno    number,
      3    ename    varchar2(10),
      4    job      varchar2(10),
      5    mgr      number,
      6    hiredate date,
      7    sal      number,
      8    comm     number,
      9    deptno   number
     10  )
     11  /
    
    Type created.
    
    SQL> CREATE OR REPLACE TYPE myrectable AS TABLE OF myemp
      2  /
    
    Type created.
    
    SQL> CREATE OR REPLACE FUNCTION pipedata(p_min_row number, p_max_row number) RETURN myrectable PIPELINED IS
      2    v_obj myemp := myemp(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
      3  BEGIN
      4    FOR e IN (select *
      5              from (
      6                    select e.*
      7                          ,rownum rn
      8                    from (select * from emp order by empno) e
      9                   )
     10              where rn between p_min_row and p_max_row)
     11    LOOP
     12      v_obj.empno    := e.empno;
     13      v_obj.ename    := e.ename;
     14      v_obj.job      := e.job;
     15      v_obj.mgr      := e.mgr;
     16      v_obj.hiredate := e.hiredate;
     17      v_obj.sal      := e.sal;
     18      v_obj.comm     := e.comm;
     19      v_obj.deptno   := e.deptno;
     20      PIPE ROW (v_obj);
     21    END LOOP;
     22    RETURN;
     23  END;
     24  /
    
    Function created.
    
    SQL> select * from table(pipedata(1,5));
    
         EMPNO ENAME      JOB               MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- ---------- ---------- -------------------- ---------- ---------- ----------
          7369 SMITH      CLERK            7902 17-DEC-1980 00:00:00        800                    20
          7499 ALLEN      SALESMAN         7698 20-FEB-1981 00:00:00       1600        300         30
          7521 WARD       SALESMAN         7698 22-FEB-1981 00:00:00       1250        500         30
          7566 JONES      MANAGER          7839 02-APR-1981 00:00:00       2975                    20
          7654 MARTIN     SALESMAN         7698 28-SEP-1981 00:00:00       1250       1400         30
    
    SQL> select * from table(pipedata(6,10));
    
         EMPNO ENAME      JOB               MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- ---------- ---------- -------------------- ---------- ---------- ----------
          7698 BLAKE      MANAGER          7839 01-MAY-1981 00:00:00       2850                    30
          7782 CLARK      MANAGER          7839 09-JUN-1981 00:00:00       2450                    10
          7788 SCOTT      ANALYST          7566 19-APR-1987 00:00:00       3000                    20
          7839 KING       PRESIDENT             17-NOV-1981 00:00:00       5000                    10
          7844 TURNER     SALESMAN         7698 08-SEP-1981 00:00:00       1500          0         30
    


    This example is for multiple columns using a defined object type. If you just need a single result column of an existing datatype you can specify that instead of the object type and table declarations.

    user13328581
  • BeefStu
    BeefStu Member Posts: 278 Blue Ribbon

    Thanks, I posted a pipe line function. I'm stuck on removing the hard coded date and calling the function

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,730 Gold Crown

    I've taken the query from your previous post, delete the "C" subquery and replaced it with a suitable call to your pipelined function:

    SELECT e.employee_id,
           c.date_val
    FROM   employees e
           INNER JOIN table(generate_dates(date '2021-07-01', DATE '2021-07-30')) 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
           )
    AND    NOT EXISTS(
             SELECT 1
             FROM   timeoff t
             WHERE  e.employee_id = t.employee_id
             AND    t.timeoff_date = c.date_val
           )
    ORDER BY
           e.employee_id,
           c.date_val
    ;
    

    Since you declared your type obj_date with an attribute date_val I've had to change all your references to c.day into c.date_val.

    Technically you haven't had to include the table() operator that I've put around the call to generate_dates() since some time in 12c, but I think it helps make it just a little more obvious what's going on in that clause.


    Regards

    Jonathan Lewis

    BeefStu
  • BEDE
    BEDE Oracle Developer Member Posts: 2,275 Gold Trophy

    Well, but the pipelined function also has a loop in it to fetch all the rows. So, you still can't do without a loop somewhere.

  • BeefStu
    BeefStu Member Posts: 278 Blue Ribbon

    @Jonathan Lewis Thanks works perfectly.