Thanks once again.

I had to exit if cursor not found stamt and it works fine now.

CREATE OR REPLACE function GetCompanies(p_where in varchar2)
return oCompanyListTable
is

rec_data oCompanyListTable:= oCompanyListTable();
l_cnt number :=0;
stmt1 varchar2(2000) := 'SELECT oCompanyList(cd.company_name,cd.registration_no,c.country,cd.amb_no,cd.life_status,c.country_code,cd .active_status,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 oCompanyList(cd.company_name,cd.registration_no,c.country,cd.amb_no,cd.life_status,c.country_code,cd .active_status,cd.alternative_company_name,cd.priority) FROM company_details cd,countries c ' ;
l_cursor types.rc;
l_rec oCompanyList;

begin

if (p_where is null)
then
open l_cursor for stmt1;
else
stmt2 := stmt2 || p_where;
open l_cursor for stmt2;
end if;

loop
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
-- dbms_output.put_line(l_rec.ocompany_name);
l_cnt := l_cnt + 1;
rec_data.extend;
rec_data(l_cnt) := l_rec;
end loop;

return rec_data;
end;
/

And yes, I will put them in a package. I was trying to get it to work first.

Thanks a lot for your response.