pl/sql creating a function with parameterized cursor with return date
- by user3134365
create or replace
FUNCTION get_next_sch_date(cert_id VARCHAR2,test_id VARCHAR2)
RETURN DATE
AS
CURSOR next_sch_date(pb_id number,test_no varchar2)
IS
SELECT Sch_Controls,PBY_FRQ,START_AFTER__CAL_DAYS,PBY_DUE_BY,PBY_NEXT_SCH_TEST_DATE FROM
ms_cmp_plan_pby WHERE pby_id=pb_id AND test_plan_id=test_no;
l_new_date DATE;
l_new_sch number;
sch_ctrl VARCHAR2(100);
pb_frq VARCHAR2(100);
start_days NUMBER;
due_days NUMBER;
test_date DATE;
pb_id NUMBER;
test_no NUMBER;
BEGIN
OPEN next_sch_date(pb_id,test_no);
loop
FETCH next_sch_date INTO sch_ctrl,pb_frq,start_days,due_days,test_date;
SELECT DISTINCT pby_rec_id INTO l_new_sch FROM ms_cmp_assignment_log WHERE
ASSIGNMENT_ID=cert_id AND PLAN_ID=test_id;
exit;
end loop;
CLOSE next_sch_date;
RETURN l_new_date;
Exception
WHEN others THEN
RETURN NULL;
end;
this is my function but i dont getting excepted result
