Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Unanswered: Using LIKE in an oracle procedure?

    Hello..

    I am trying to design a procedure that will update a column if its value is like
    one of the inputs. The column is a varchar that has a list. Here is an example of my table:

    id SEQUENCE
    -------------------------
    1 1.02,1.03,1.04
    2 2.02,2.03,2.04

    My procedure is:

    CREATE or REPLACE PROCUDURE UpdateSeq (oldseq IN varchar, newseq in varchar) IS
    BEGIN
    update "SEQ"
    set "SEQUNCE" = "SEQUNCE" + ',' + newseq
    where "SEQUENCE" like oldseq;
    END;
    I call the proc using:

    exec UpdateSeq('1.02','3.01')

    The expected result would be to set the first row equal to its current contents plus ',3.01' since it meets the like criteria. However, this doesnt happen.

    What am I doing wrong? Can I use like in this fashion in a proc?

    Thanks in advance!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You need to add '%':

    ...
    where "SEQUENCE" like oldseq||'%'

  3. #3
    Join Date
    Oct 2003
    Posts
    8
    Thanks for the quick response, but now when I call it I get an error:

    ORA-01722: invalid number
    ORA-06512: at "TEST1.UPDATESEQ", line 3



    any advice?

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You could have also call it like.. exec UpdateSeq('1.02%','3.01').

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The concatenate operator is || not +

  6. #6
    Join Date
    Oct 2003
    Posts
    8
    Thanks so much Tony, it works perfectly!

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    I wouldn't recommend naming columns the same name as oracle objects
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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