Need help in using ref cursor for this function

[thomasp]

Hi,

Can you please help me with this function?

CREATE OR REPLACE function TestGetCompanies(p_where in varchar2)
return oCompanyListTable
is
rec_data oCompanyListTable:= oCompanyListTable();
l_cnt number :=0;
--stmt1 varchar2(2000) := 'SELECT cd.company_name,cd.registration_no,c.country,cd.amb_no,cd.life_status,c.country_code,cd.active_statu s,cd.alternative_company_name,cd.priority FROM company_details cd,countries c WHERE c.country_code(+)=cd.country_code ORDER BY cd.company_name';
--stmt2 varchar2(4000) := 'SELECT cd.company_name,cd.registration_no,c.country,cd.amb_no,cd.life_status,c.country_code,cd.active_statu s,cd.alternative_company_name,cd.priority FROM company_details cd,countries c ' ;
cursor c1 is SELECT cd.company_name,cd.registration_no,c.country,cd.amb_no,cd.life_status,c.country_code,cd.active_statu s,cd.alternative_company_name,cd.priority FROM company_details cd,countries c WHERE c.country_code(+)=cd.country_code ORDER BY cd.company_name;
cursor c2 is SELECT cd.company_name,cd.registration_no,c.country,cd.amb_no,cd.life_status,c.country_code,cd.active_statu s,cd.alternative_company_name,cd.priority FROM company_details cd,countries c WHERE (SELECT COUNT(*) FROM company_year_ends WHERE amb_no=cd.amb_no AND ((analyst_name='Smith John' AND nvl(signed_off,'0')=0 AND date_completed IS NULL) OR (fsa_analyst_name='Smith John' AND fsa_date_completed IS NULL)) AND ROWNUM=1)=1 AND c.country_code(+)=cd.country_code ORDER BY cd.company_name;
--type rc is ref cursor;
--c2 rc;
begin
if (p_where='a')
then
--for x in stmt1 [How do I write this part?]
for x in c1
loop
l_cnt:= l_cnt + 1;
rec_data.extend;
rec_data(l_cnt):= oCompanyList(x.company_name,x.registration_no,x.country,x.amb_no,x.life_status,x.country_code,
x.active_status,x.alternative_company_name,x.priority);
end loop;
else

--open c2 for stmt2;
--stmt2:= stmt2 || p_where;[ how do I write part as I will be passing the where clause here in p_where]
--open c2 for stmt2 ;
--fetch c2 into c2_pos;
--for x in select cursor(stmt2)
for x in c2
loop
l_cnt:= l_cnt + 1;
rec_data.extend;
rec_data(l_cnt):= oCompanyList(x.company_name,x.registration_no,x.country,x.amb_no,x.life_status,x.country_code,
x.active_status,x.alternative_company_name,x.priority);
end loop;

end if;
return rec_data;

end;

Two of the things I need help with is:
1) need to use ref cursor here but I'm not sure how to use
for ref_cur in stmt1 (this was giving me an error) and how to get the return data
2) I need to pass the where clause in p_where. How do I go about doing that.

I'm not very good in this area and I'd appreciate any help.
Thanks a lot for all the help.