Discussions
Categories
- 385.5K All Categories
- 4.9K Data
- 2.5K Big Data Appliance
- 2.4K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Oracle returning a cursor from a function or procedure

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