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.

[thomasp]

Sorry , I attached the wrong version file.
Please find the function script attached below.

Many Thanks.

[Srinivas_Sharma]

CREATE OR REPLACE PACKAGE Mypkg
AS

TYPE cursor_type IS REF CURSOR;

Procedure TestGetCompanies(condition in number,p_cursor in out cursor_type)

End;

CREATE OR REPLACE PACKAGE BODY Mypkg
AS

Procedure TestGetCompanies(condition in number,p_cursor in out cursor_type)

if condition=1 then
open p_cursor for "Select Statement"
else
open p_cursor for "Select another Statement"

end if;

try with the above thing,,it should work.....


Srini

[thomasp]

Thanks for the reply.

Ideally I would like it to look like this and this is how I finally wrote it yesterday.

The first part of the if statement works, but the second part just goes off into some sort of loop and hangs the whole database. Can you help me write this correctly and if it doesnt work, I'll try to put them in the package passing an in parameter.


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;

loop
fetch l_cursor into l_rec;
l_cnt := l_cnt + 1;
rec_data.extend;
rec_data(l_cnt) := l_rec;
end loop;

end if;
return rec_data;
end;
/

Many Thanks.

[Srinivas_Sharma]

Hi,

I cant check the code at my place.

If you are using ref cursors, best is to put in a package and call them. Its a good practice to have package and put procedures or functions inside it.

Use it the way I suggested and it should work.

Hey See to it first if your SQL returns the right resultset or not.

HTH

Srini

[thomasp]

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.