Hi all...

Here in work trying to help a friend, she wants to make a ref cursor to return it from a Package procedure... the problem is that WHERE CLAUSE of this cursor is dynamic, and i don't know how make it...
I supose she can prepare an IF, ELSIF, ELSE Sequence but don't seems too much elegant...

Any idea...

Here the code she gave me

Thx in advance

procedure GetPersonsByName(pCursor out c_cursor,
pNName in PRPersons.NName%type,
pNMiddleName in PRPersons.NMiddleName%type,
pNLastName in PRPersons.NLastName%type)
is
v_Where varchar2(500);
begin

v_Where := '';
if (pNName.Length > 0) then
v_Where := ' NName like ' || pNName;
end if;

if (pNMiddleName.Lentgh > 0) then
if (v_Where.Length = 0) then
v_Where := ' NMiddleName like ' || pNMiddleName;
else
v_Where := v_Where || ' and NMiddleName like ' || pNMiddleName;
end if;
end if;

if (pNLastName.Lentgh > 0) then
if (v_Where.Length = 0) then
v_Where := ' NLastName like ' || pNLastName;
else
v_Where := v_Where || ' and NLastName like ' || pNLastName;
end if;
end if;


open pCursor for
select
PersonId,
CountryId,
Nif,
PostalTypeEnum,
TitleTypeEnum,
Name,
MiddleName,
LastName,
NName,
NMiddleName,
NLastName,
Sex,
Enabled,
DisableDate,
BirthDate,
VersionId
from
PRPersons
where v_Where
order by NName,NMiddleName,NLastName
;


end GetPersonsByName;