REF Cursor... Dynamic WHERE Clause
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: REF Cursor... Dynamic WHERE Clause

Hybrid View

  1. #1
    Join Date
    Dec 2002
    Location
    In your worst nightmares :P
    Posts
    5

    REF Cursor... Dynamic WHERE Clause

    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;

  2. #2
    Join Date
    Dec 2002
    Location
    In your worst nightmares :P
    Posts
    5
    Pls close the post... she used the .length property instead of length() function (she uses to program in Visual Basic)

    Sorry to all

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width