SQL & PL/SQL

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;

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

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;

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

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

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.

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

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

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.

@Jonathan Lewis Thanks works perfectly.

@bede,
There's always the case of "horses for courses". You always pay something somewhere.
The benefit of the pipelined function is that you only write the complicated bit once, even though it costs you in row-by-row processing. You might note, though, that the OP's query had an output of 83 rows (when I ran it), which is a lot more row-by-row than the 7 rows that had to be processed in the function.
An alternative (since the OP may be on a sufficiently recent releast of 19c (19.10 might be okay, I've just tested 19.11) is the SQL Macro:

create or replace function generate_dates(
        p_from  in date,
        p_to    in date
)
return varchar2
sql_macro    -- NOTE THIS
is
        v_sql varchar2(4000);
begin
        v_sql := '
                with calendar (start_date, end_date ) as (
                        select date ''' ||
                        to_char(p_from,'yyyy-mm-dd') ||
                        ''' start_date, date ''' ||
                        to_char(p_to  ,'yyyy-mm-dd') ||
                        ''' end_date from dual ' ||
                        'union all
                        select start_date + 1, end_date
                        from   calendar
                        where  start_date + 1 <= end_date
                )
                select start_date as date_val
                from   calendar
        ';

--      dbms_output.put_line(v_sql);
        return v_sql;

end generate_dates;
/

This has the effect of allowing the complex code to be written once (and then made totally incomprehensible because of the need to make it a generated string!) and used many times. The call to the function effectively generates the correct inline text before the optimizer does a pure SQL optimisation.
(As it stands creating this function and then re-running the final SQL with "date_val" will get the correct result with no row-by-row procssing).

Regards
Jonathan Lewis

@beefstu
Thanks for the feedback.
If you're running a recent release of 19c then there is another option available - the SQL Macro - which might have been Oracle Corps. response to exactly this type of problem.
Regards
Jonathan Lewis

[Jonathan Lewis](/ords/forums/user/Jonathan Lewis) Regarding your SQL Macro example:
I don't think it's needed to format the incoming arguments and concatenate it in the SQL statement, I think you can reference the incoming arguments directly like:

create or replace function generate_dates(
    p_from in date,
    p_to  in date
)
return varchar2
sql_macro
is
    v_sql varchar2(4000);
begin
    v_sql := '
        with calendar (start_date, end_date ) as (
            select generate_dates.p_from as start_date --<-- directly referencing the argument
               ,generate_dates.p_to as end_date  --<-- 
            from dual 
            union all
            select start_date + 1, end_date
            from  calendar
            where start_date + 1 <= end_date
        )
        select start_date as date_val
        from  calendar
    ';
    return v_sql;

end generate_dates;
/

Only problem is that I don't have access to any other version than a Oracle 19.0... and in that version there is a bug where you can't use the WITH clause (Bug 32212976: USING SCALAR ARGUMENTS IN WITH CLAUSE IN SQL TABLE MACRO RAISES ORA-06553 PLS-306 )
So I had to rewrite it to not use recursive subquery factoring to "prove my point"

create or replace function generate_dates(
    p_from in date,
    p_to  in date
)
return varchar2
sql_macro  -- NOTE THIS
is
    v_sql varchar2(4000);
begin
  v_sql := 'select trunc (generate_dates.p_from) -1 + level as startdate
       from dual
       connect by level <= (generate_dates.p_to - generate_dates.p_from)';

    return v_sql;

end generate_dates;
/

Alex,
Thanks for that - it didn't occur to me that the problem was the WITH subquery (my testbed is 19.11.0.0), so I switched to this messy character substitution without thinking about why I was getting that ORA-06553 error. (The substitution method I used on the corresponding blog note - which is in its final draft ***, but which is now going to have an update - is a little tidier than this one.)
Regards
Jonathan Lewis

*** Update: published at https://jonathanlewis.wordpress.com/2021/07/22/sql-macro/

@Johnathan Lewis I modified my code to use your new function generate_dates_pipelined and it works great!!! Kudos for the excellent idea and detailed write up.

A quick question if you don't mind. In order to make the calling code truly generic would it make sense to pull out the hard coded dates and do something like this first in order not to possibly mess up the SQL by editing it every time it needs to run.

This way the caller can always pass pipelined_start_date, pipelined_end_date. Your thoughts please. Using bind variables would require input from the user and it could not be run non interactively.
variable pipelined_start_date varchar2(30)
variable pipelined_end_date varchar2(30)
exec :pipelined_start_date := '20210722';
exec :pipelined_end_date := '20210731';
Or set to SYSDATE, SYSDATE + N

@beefstu
That seems perfectly reasonable to me; the pipeline method certainly works with incoming bind variables, and it would probably have some effect of reducing total parse costs.
Regards
Jonathan Lewis