Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: Oracle 8.1.7 SP and IN operator

    Hello,

    How can I use the IN operator and send a comma separated list as a SP parameter?

    I tried to do something like this, but it failed

    update table
    set state = 'N'
    where rowid in (pValues)

    On one of the newsgroups I read that the above is not supported. Is this correct? What is a workaround?

    Kind regards,
    Ronan van Riet

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    No quick solution... and I do believe what you read in the newsgroups is correct.

    I would create a function that would accept your list, so change your update to something like this:

    Code:
    update table
    set state = 'N'
    where find_me(pValues, myValue)
    where...

    Code:
    function find_me (values_p varchar2, rowid_p varchar2 )
    return boolean
    is
    begin
       return( values_p like ( '%' || rowid_p || '%') );
    end;
    That's basically it... you would probably want to do more to ensure that sub string matches don't return true, for instance, not returning a match if pValues = 'ABC,DEF...' and myValue = 'A'.

    Hope this sends you in the right direction.

    JoeB
    Last edited by joebednarz; 01-05-04 at 23:39.

Posting Permissions

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