You Asked
SUBJECT: Return a PL/SQL table from a function as a REF CURSOR
Tom,
Ive just discovered your site What a great resource, thanks!
Im having some trouble with PL/SQL TABLES and REF CURSORS. I would like to return a PL/SQL table from a function as a REF CURSOR. I wrote my code based upon some of your archive examples. For some reason, my REF CURSOR returns from the function without data. Ive been trying to test the functionality using SQLPlus. Here is what I have:
Type creations:
CREATE OR REPLACE PACKAGE Types
IS
TYPE REF_CURSOR IS REF CURSOR;
TYPE EMP_APPROVAL_RECORD IS RECORD(
empid VARCHAR2(10),
empname VARCHAR2(50),
approved_hours NUMBER(6,2),
non_approved_hours NUMBER(6,2)
);
END;
/
CREATE OR REPLACE TYPE APPROVAL_RECORD IS OBJECT(
empid VARCHAR2(10),
empname VARCHAR2(50),
approved_hours NUMBER(6,2),
non_approved_hours NUMBER(6,2)
);
/
The function:
CREATE OR REPLACE FUNCTION GET_APPROVAL_INFO (x_deptid NUMBER, x_bdate VARCHAR2, x_edate VARCHAR2)
RETURN TYPES.REF_CURSOR
IS
approval_data TYPES.REF_CURSOR;
emp_data TYPES.EMP_APPROVAL_RECORD;
dept_data TBL_APPROVAL_DATA := TBL_APPROVAL_DATA();
rec_count NUMBER DEFAULT 0;
x_timeuserid VARCHAR2(10);
x_holduserid VARCHAR2(10);
CURSOR emp_in_dept IS
SELECT TIMEUSERID
FROM TIMEUSER
WHERE DeptID = x_deptid
ORDER BY TIMEUSERID;
FUNCTION get_emp_data (personid TimeUser.TimeUserID%TYPE, bdate VARCHAR2, edate VARCHAR2)
RETURN TYPES.EMP_APPROVAL_RECORD
IS
return_data TYPES.EMP_APPROVAL_RECORD;
BEGIN
SELECT A.TIMEUSERID, A.FIRSTNAME || ' ' || A.LASTNAME AS FULLNAME,
((SELECT 0
FROM TIMEUSER
WHERE TIMEUSER.TIMEUSERID = personid
AND NOT EXISTS(
SELECT TIMEUSER.TIMEUSERID FROM TIMEUSER,TIMECARD, CARDROWS, CARDHOURS
WHERE TIMEUSER.TIMEUSERID = TIMECARD.TIMEUSERID
AND TIMECARD.TIMECARDID = CARDROWS.TIMECARDID
AND CARDROWS.CARDROWID = CARDHOURS.CARDROWID
AND TIMEUSER.TIMEUSERID = personid
AND CardHours.TaskDate >= TO_DATE(bdate,'mm/dd/yyyy')
AND CardHours.TaskDate <= TO_DATE(edate,'mm/dd/yyyy')
AND CARDHOURS.APPROVERID IS NOT NULL))
UNION ALL
(SELECT SUM(HOURS)
FROM TIMEUSER, TIMECARD, CARDROWS, CARDHOURS
WHERE TIMEUSER.TIMEUSERID = TIMECARD.TIMEUSERID
AND TIMECARD.TIMECARDID = CARDROWS.TIMECARDID
AND CARDROWS.CARDROWID = CARDHOURS.CARDROWID
AND TIMEUSER.TIMEUSERID = personid
AND CardHours.TaskDate >= TO_DATE(bdate,'mm/dd/yyyy')
AND CardHours.TaskDate <= TO_DATE(edate,'mm/dd/yyyy')
AND CARDHOURS.APPROVERID IS NOT NULL
GROUP BY TIMEUSER.TIMEUSERID, FIRSTNAME, LASTNAME)) AS APPROVED
,
((SELECT 0
FROM TIMEUSER
WHERE TIMEUSER.TIMEUSERID = personid
AND NOT EXISTS(
SELECT TIMEUSER.TIMEUSERID
FROM TIMEUSER,TIMECARD, CARDROWS, CARDHOURS
WHERE TIMEUSER.TIMEUSERID = TIMECARD.TIMEUSERID
AND TIMECARD.TIMECARDID = CARDROWS.TIMECARDID
AND CARDROWS.CARDROWID = CARDHOURS.CARDROWID
AND TIMEUSER.TIMEUSERID = personid
AND CardHours.TaskDate >= TO_DATE(bdate,'mm/dd/yyyy')
AND CardHours.TaskDate <= TO_DATE(edate,'mm/dd/yyyy')
AND CARDHOURS.APPROVERID IS NULL))
UNION ALL
(SELECT SUM(HOURS)
FROM TIMEUSER, TIMECARD, CARDROWS, CARDHOURS
WHERE TIMEUSER.TIMEUSERID = TIMECARD.TIMEUSERID
AND TIMECARD.TIMECARDID = CARDROWS.TIMECARDID
AND CARDROWS.CARDROWID = CARDHOURS.CARDROWID
AND TIMEUSER.TIMEUSERID = personid
AND CardHours.TaskDate >= TO_DATE(bdate,'mm/dd/yyyy')
AND CardHours.TaskDate <= TO_DATE(edate,'mm/dd/yyyy')
AND CARDHOURS.APPROVERID IS NULL
GROUP BY TIMEUSER.TIMEUSERID, FIRSTNAME, LASTNAME)) AS NON_APPROVED
INTO return_data
FROM TIMEUSER A
WHERE A.TIMEUSERID = personid;
RETURN return_data;
END get_emp_data;
BEGIN
x_holduserid := '';
OPEN emp_in_dept;
LOOP
FETCH emp_in_dept INTO x_timeuserid;
rec_count := rec_count + 1;
IF x_timeuserid = x_holduserid THEN
x_holduserid := x_timeuserid;
ELSE
-- processing logic for each employee
emp_data := get_emp_data(x_timeuserid, x_bdate, x_edate);
-- move data to table which will be returned by this function to java servlet
dept_data.extend;
dept_data(rec_count) := APPROVAL_RECORD(emp_data.empid,
emp_data.empname,
emp_data.approved_hours,
emp_data.non_approved_hours);
x_holduserid := x_timeuserid;
END IF;
EXIT WHEN emp_in_dept%NOTFOUND;
END LOOP;
CLOSE emp_in_dept;
OPEN approval_data FOR
SELECT * FROM TABLE ( cast (dept_data AS TBL_APPROVAL_DATA) );
EXCEPTION
WHEN NO_DATA_FOUND THEN
Null;
WHEN OTHERS THEN
Null;
RETURN approval_data;
END GET_APPROVAL_INFO;
/
The problem:
Is there something Im doing incorrectly? Ive been trying to follow the previous examples in your archives. Obviously, there must be something wrong with OPEN approval_data FOR
SELECT * FROM TABLE ( cast (dept_data AS TBL_APPROVAL_DATA) );
Heres how I tested it in SQLPlus:
SQL> var x refcursor
SQL> exec :x := get_approval_info(1,'1/1/2003','1/31/2003')
BEGIN :x := get_approval_info(1,'1/1/2003','1/31/2003'); END;
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "TIME.GET_APPROVAL_INFO", line 125
ORA-06512: at line 1
SQL> spool off
Successful data test:
The SQL Ive used to test that the PL/SQL TABLE does indeed get populated:
set serveroutput on;
execute dbms_output.disable;
execute dbms_output.enable;
DECLARE
x_sqlcode number;
x_sqlerr char(512);
x_deptid number;
x_bdate varchar2(10);
x_edate varchar2(10);
rec_count NUMBER DEFAULT 0;
cursor_count NUMBER DEFAULT 0;
x_timeuserid varchar2(10);
x_holduserid varchar2(10);
approval_data TYPES.REF_CURSOR;
emp_data TYPES.EMP_APPROVAL_RECORD;
dept_data TBL_APPROVAL_DATA := TBL_APPROVAL_DATA();
CURSOR emp_in_dept IS
SELECT TIMEUSERID
FROM TIMEUSER
WHERE DeptID = x_deptid
ORDER BY TIMEUSERID;
FUNCTION get_emp_data (personid TimeUser.TimeUserID%TYPE, bdate VARCHAR2, edate VARCHAR2)
RETURN TYPES.EMP_APPROVAL_RECORD
IS
return_data TYPES.EMP_APPROVAL_RECORD;
BEGIN
SELECT DISTINCT A.TIMEUSERID, A.FIRSTNAME || ' ' || A.LASTNAME AS FULLNAME,
((SELECT 0
FROM TIMEUSER
WHERE TIMEUSER.TIMEUSERID = personid
AND NOT EXISTS(
SELECT TIMEUSER.TIMEUSERID FROM TIMEUSER,TIMECARD, CARDROWS, CARDHOURS
WHERE TIMEUSER.TIMEUSERID = TIMECARD.TIMEUSERID
AND TIMECARD.TIMECARDID = CARDROWS.TIMECARDID
AND CARDROWS.CARDROWID = CARDHOURS.CARDROWID
AND TIMEUSER.TIMEUSERID = personid
AND CardHours.TaskDate >= TO_DATE(bdate,'mm/dd/yyyy')
AND CardHours.TaskDate <= TO_DATE(edate,'mm/dd/yyyy')
AND CARDHOURS.APPROVERID IS NOT NULL))
UNION ALL
(SELECT SUM(HOURS)
FROM TIMEUSER, TIMECARD, CARDROWS, CARDHOURS
WHERE TIMEUSER.TIMEUSERID = TIMECARD.TIMEUSERID
AND TIMECARD.TIMECARDID = CARDROWS.TIMECARDID
AND CARDROWS.CARDROWID = CARDHOURS.CARDROWID
AND TIMEUSER.TIMEUSERID = personid
AND CardHours.TaskDate >= TO_DATE(bdate,'mm/dd/yyyy')
AND CardHours.TaskDate <= TO_DATE(edate,'mm/dd/yyyy')
AND CARDHOURS.APPROVERID IS NOT NULL
GROUP BY TIMEUSER.TIMEUSERID, FIRSTNAME, LASTNAME)) AS APPROVED
,
((SELECT 0
FROM TIMEUSER
WHERE TIMEUSER.TIMEUSERID = personid
AND NOT EXISTS(
SELECT TIMEUSER.TIMEUSERID
FROM TIMEUSER,TIMECARD, CARDROWS, CARDHOURS
WHERE TIMEUSER.TIMEUSERID = TIMECARD.TIMEUSERID
AND TIMECARD.TIMECARDID = CARDROWS.TIMECARDID
AND CARDROWS.CARDROWID = CARDHOURS.CARDROWID
AND TIMEUSER.TIMEUSERID = personid
AND CardHours.TaskDate >= TO_DATE(bdate,'mm/dd/yyyy')
AND CardHours.TaskDate <= TO_DATE(edate,'mm/dd/yyyy')
AND CARDHOURS.APPROVERID IS NULL))
UNION ALL
(SELECT SUM(HOURS)
FROM TIMEUSER, TIMECARD, CARDROWS, CARDHOURS
WHERE TIMEUSER.TIMEUSERID = TIMECARD.TIMEUSERID
AND TIMECARD.TIMECARDID = CARDROWS.TIMECARDID
AND CARDROWS.CARDROWID = CARDHOURS.CARDROWID
AND TIMEUSER.TIMEUSERID = personid
AND CardHours.TaskDate >= TO_DATE(bdate,'mm/dd/yyyy')
AND CardHours.TaskDate <= TO_DATE(edate,'mm/dd/yyyy')
AND CARDHOURS.APPROVERID IS NULL
GROUP BY TIMEUSER.TIMEUSERID, FIRSTNAME, LASTNAME)) AS NON_APPROVED
INTO return_data
FROM TIMEUSER A
WHERE A.TIMEUSERID = personid;
RETURN return_data;
END get_emp_data;
BEGIN
x_deptid := 1;
x_bdate := '01/01/2003';
x_edate := '01/31/2003';
x_holduserid := '';
OPEN emp_in_dept;
LOOP
FETCH emp_in_dept INTO x_timeuserid;
rec_count := rec_count + 1;
IF x_timeuserid = x_holduserid THEN
x_holduserid := x_timeuserid;
ELSE
-- dbms_output.put_line (x_timeuserid);
-- processing logic for each employee
emp_data := get_emp_data(x_timeuserid, x_bdate, x_edate);
dept_data.extend;
/* dept_data(rec_count).empid := emp_data.empid;
dept_data(rec_count).empname := emp_data.empname;
dept_data(rec_count).approved_hours := emp_data.approved_hours;
dept_data(rec_count).non_approved_hours := emp_data.non_approved_hours; */
dept_data(rec_count) := APPROVAL_RECORD(emp_data.empid,
emp_data.empname,
emp_data.approved_hours,
emp_data.non_approved_hours);
dbms_output.put_line('Dept_Data(' || rec_count || ')');
dbms_output.put_line('Employee: ' || dept_data(rec_count).empname);
dbms_output.put_line('Approved: ' || dept_data(rec_count).approved_hours);
dbms_output.put_line('Non-approved: ' || dept_data(rec_count).non_approved_hours);
dbms_output.put_line('++++++++++++++++++++++++++++++++');
x_holduserid := x_timeuserid;
END IF;
EXIT WHEN emp_in_dept%NOTFOUND;
END LOOP;
CLOSE emp_in_dept;
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_sqlcode := sqlcode;
x_sqlerr := sqlerrm;
dbms_output.put_line(x_sqlcode || ' ' || substr(x_sqlerr,1,70));
WHEN TOO_MANY_ROWS THEN
x_sqlcode := sqlcode;
x_sqlerr := sqlerrm;
dbms_output.put_line(x_sqlcode || ' ' || substr(x_sqlerr,1,70));
WHEN OTHERS THEN
x_sqlcode := sqlcode;
x_sqlerr := sqlerrm;
dbms_output.put_line(x_sqlcode || ' ' || substr(x_sqlerr,1,70));
END;
/
-------------------------------------------------------------------
Data results:
SQL> @pl_approval4
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Dept_Data(1)
Employee: ART HALSTEAD
Approved: 0
Non-approved: 119.5
++++++++++++++++++++++++++++++++
Dept_Data(2)
Employee: ALAN HOLLINGS
Approved: 127.5
Non-approved: 0
++++++++++++++++++++++++++++++++
Data continues... but no need to print it all here.
and Tom said...
You have a function:
CREATE OR REPLACE FUNCTION GET_APPROVAL_INFO (x_deptid NUMBER, x_bdate VARCHAR2,
x_edate VARCHAR2)
RETURN TYPES.REF_CURSOR
....
that ends like this:
OPEN approval_data FOR
SELECT * FROM TABLE ( cast (dept_data AS TBL_APPROVAL_DATA) );
EXCEPTION
WHEN NO_DATA_FOUND THEN
Null;
WHEN OTHERS THEN
Null;
RETURN approval_data;
END GET_APPROVAL_INFO;
/
(*when others null* -- HORRIBLE, delete it now!@!!!!!!!!!!!! seriously, delete it NOW)
You only return something UPON ERROR.
Problem is -- your code is not getting any errors!~
so you return *nothing*
(also, you do realize you are processing the last record twice!!
open c;
loop
fetch c into record;
process...
exit when c%notfound
end loop
processes the last fetched record twice (or if no records are returned, it processes a NULL record that never really existed once!)
open c;
loop
fetch c into record;
EXIT WHEN C%NOTFOUND;
process
end loop
is the correct way....
also, you don't need any procedural code whatsoever, this should be a single query.
is
begin
open ref_cursor for
SELECT A.TIMEUSERID, A.FIRSTNAME || ' ' || A.LASTNAME AS FULLNAME,
((SELECT 0... GROUP BY TIMEUSER.TIMEUSERID, FIRSTNAME, LASTNAME)) AS APPROVED,
((SELECT 0 .... BY TIMEUSER.TIMEUSERID, FIRSTNAME, LASTNAME)) AS NON_APPROVED
FROM (select distinct timeuserid, firename, lastname
from TIMEUSER where deptid = x_deptid ) A
order by timeuserid;
return ref_cursor;
end;
thats all the code you need
You are getting the distinct timeuserids (hold/current seem to be trying to do that, skip over "dups" -- SQL can dedup for you) and then looking up data (sql is great for looking up, it is what it does best) and returning an ordered set.
That query does that.
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment