Creating a function with cursor with parameter example
create or replace
FUNCTION xx_get_serial_number(p_del_detail_ID IN NUMBER) RETURN VARCHAR2 IS
CURSOR get_ser_num(C_delivery_detail_ID NUMBER) IS
select fm_serial_number
from WSH_SERIAL_NUMBERS
where delivery_detail_ID = C_delivery_detail_ID; --32299178
l_result varchar2(32767);
l_value varchar2(32767) := NULL;
l_ser_num varchar2(120);
i number :=1;
BEGIN
FOR GET_SER_NUM_REC IN GET_SER_NUM(p_del_detail_ID)
LOOP
l_ser_num := GET_SER_NUM_REC.fm_serial_number;
if l_ser_num is not null then
IF i=1 then
l_value:=l_ser_num;
elsif i mod 4 !=0 then
l_value := l_value ||' '||','|| l_ser_num ;
else
l_value := l_value ||' '||','|| l_ser_num||chr(9) ;
end if;
i := i +1;
end if;
END LOOP;
RETURN (l_value);
END;
