title: Using a parameter as part of a SQL query inside stored procedure Oracle [homework]
body: <p>I am a bit confused about Stored procedures and using cursors to run SELECT for multiple rows. I have a need to use the LIKE clause inside a SQL query. I have tested that this is in fact working with my stored procedure:</p>
  
  <pre><code>SQL&gt; /* THIS WORKS*/
  SQL&gt; create or replace procedure search_testimonials(
    2  curRETURN OUT sys_refcursor
    3  )
    4  IS
    5  begin
    6   OPEN curRETURN FOR 
    7   SELECT testimonial
    8   FROM testimonial
    9   WHERE testimonial like '%like%';
   10  END search_testimonials;
   11  /
  
  Procedure created.
  
  SQL&gt; variable buffer refcursor;
  SQL&gt; execute Search_Testimonials(:buffer);
  
  PL/SQL procedure successfully completed.
  
  SQL&gt; print buffer;
  
  TESTIMONIAL
  --------------------------------------------------------------------------------
  I like the way OAG does business. I will be a repeat customer
  I like the Cashier named David. I think he is a hottie! Go AOGS
  </code></pre>
  
  <p>The problem arises when I try to use a parameter as part of my search term:</p>
  
  <pre><code>SQL&gt; /* THIS DOES NOT WORK*/
  SQL&gt; create or replace procedure search_testimonials(
    2  search_term IN varchar2,
    3  curRETURN OUT sys_refcursor
    4  )
    5  IS
    6  begin
    7   OPEN curRETURN FOR 
    8   SELECT testimonial
    9   FROM testimonial
   10   WHERE testimonial like search_term||'%%';
   11  END search_testimonials;
   12  /
  
  Procedure created.
  
  SQL&gt; 
  SQL&gt; execute Search_Testimonials('like', :buffer);
  
  PL/SQL procedure successfully completed.
  
  SQL&gt; print buffer;
  
  no rows selected
  </code></pre>
  
  <p>I have no idea why this does not work. I have also tried to use the @VARIABLE notation to pass the proper term. I am using SQLPlus to run the code. Thank you for any suggestions.</p>