title: Using String Concatenation in Oracle dynamic SQL
body: <p>I am trying to return a cursor from Oracle Stored Procedure. In my SP I am creating a dynamic SQL but getting below error:</p>
  
  <p><strong>ORA-00604: error occurred at recursive SQL level 1<br>
  ORA-01003: no statement parsed</strong></p>
  
  <p>Here is my code, the error comes only when I use pipes to add variables in my SQL</p>
  
  <pre><code>PROCEDURE GET_EXP_INV_RECORD
  (
  OUT_REVENUE_DETAILS_CUR OUT OUTCURSOR, --  TYPE OUTCURSOR IS REF CURSOR
  IN_SEQ_NO IN SOME_TABLE.SEQ#%TYPE
  )AS
   L_SQL_QUERY varchar2(32000);
   L_LOC_BLR VARCHAR2(50)     := 'Bangalore';
   L_LOC_PUNE VARCHAR2(50)    := 'Pune';
   L_LOC_MAS VARCHAR2(50)     := 'Chennai';
  
  
   L_SQL_QUERY :=     'SELECT   
  CASE WHEN CONDITION1  THEN  ' ||  L_LOC_BLR || -- POTENTIAL ERROR SOURCE
   ' WHEN CONDITION2  THEN  ' || L_LOC_PUNE   ||
   ' ELSE    ' || L_LOC_MAS ||  
   ' END AS LOCATION FROM MY_TABLE WHERE SOME_COL = SOME_VAL';
  
   OPEN OUT_REVENUE_DETAILS_CUR FOR L_SQL_QUERY;
  EXCEPTION
    WHEN OTHERS THEN
    -- LOG THE ERROR
    ROLLBACK;
    RETURN; 
  END GET_EXP_INV_RECORD;
  </code></pre>
  
  <p>If I replace the variable <em>L_LOC_BLR</em> with actual string it works fine.
     Can you please help me figure out the reason for this error.</p>
  
  <p>Thanks</p>