LIKE clause in PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: LIKE clause in PL/SQL

Hybrid View

  1. #1
    Join Date
    Jun 2003
    Posts
    2

    LIKE clause in PL/SQL

    Hi,
    Need help in this. I have a procedure which is passed a varchar parameter. It needs to do a SELECT on a table to get all rows where Name like the passed name. Here is a code I wrote:

    PROCEDURE GET_NAME
    (
    p_name IN VARCHAR2
    ,p_cursor OUT dnCursor
    )

    IS

    BEGIN
    OPEN p_cursor FOR
    SELECT
    * FROM Table WHERE Name LIKE '%' + p_name + '%'
    END GET_NAME;

    This returns a invalid number error.

    Whats wrong here..

    Please advise
    Thanks
    orauser

  2. #2
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Does this select work out of PL/SQL?


    + is not string concatenation character, || is
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  3. #3
    Join Date
    Oct 2002
    Location
    Ljubljana,Slovenia
    Posts
    28
    Instead
    SELECT
    * FROM Table WHERE Name LIKE '%' + p_name + '%'

    use

    SELECT
    * FROM Table WHERE Name LIKE '''%' || p_name || '%'''
    Aleš Orehek

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I don't think so. That will only match patterns that start and end with a single-quote
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jun 2003
    Posts
    2

    Unhappy

    Thanks for the reply. Sorry I am new to Oracle..
    Trying to execute the procedure
    I give this

    execute PakageName.ProcedureName('Name_to_be_searched', dnOutCursor);

    I get invalid SQL error

    How do initialize the output cursor here and execute it?

    THanks Again

    orauser73

  6. #6
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    Hi

    say at the sqlplus prompt

    var c refcursor

    exec PackageName.ProcedureName('Name_to_be_searched', :c);

    print c - this will output ur cursor values


    HTH

    SS

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