CURSOR WITH PARAMETER
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: CURSOR WITH PARAMETER

  1. #1
    Join Date
    Nov 2000
    Posts
    65

    Lightbulb


    Hey bright guys !! you sould know this

    CURSOR report_cursor(v_namesearch VARCHAR2) IS
    SELECT name, address
    FROM emp AND name LIKE 'v_namesearch%';

    There seems to be something wrong with the way I have defined the above cursor. Looks like, since v_namesearch is
    a variable (& not a constant) , the singe quotes seems to be messing up with what I am trying to accomplish.

    Shall appreciate any help.. as I seem to be stuck with this
    issue >> >>

    Thank you & Merry X'mas
    -NK

  2. #2
    Join Date
    Nov 2000
    Posts
    65

    Unhappy

    CURSOR report_cursor(v_namesearch VARCHAR2) IS
    SELECT name, address
    FROM emp WHERE name LIKE 'v_namesearch%';

    Pardon my typing the
    the cursor declaration should read as above

    thank,
    nk


  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    CURSOR report_cursor(v_namesearch VARCHAR2) IS
    SELECT name, address
    FROM emp WHERE name LIKE v_namesearch || '%';

    Anything inside a quote is taken as a literal. Therefore, you cannot embed variables inside quotes.

    Hope this helps,

    - Chris

  4. #4
    Join Date
    Nov 2000
    Posts
    65

    thanks

    THANKS Chris..
    But this doesn't seem to work at first glance. I will dig deeper!!
    In the meanwhile let me share my views.

    the clause
    LIKE v_namesearch || '%';
    seems to be the problem. May be because v_namesearch is a string variable, it also needs a single quotes. But how??

    Any ideas.

    nk





  5. #5
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    This should work:

    LIKE ' '%'||v_namesearch||'%' ';

  6. #6
    Join Date
    Oct 2000
    Posts
    123
    Try:

    LIKE ''''|| '%'||v_namesearch||'%'||'''' OR
    LIKE '''%'||v_namesearch||'%''';

    Basically, '' represents " ' " .


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