Using String Concatenation in Oracle dynamic SQL

Question

I am trying to return a cursor from Oracle Stored Procedure. In my SP I am creating a dynamic SQL but getting below error:

ORA-00604: error occurred at recursive SQL level 1
ORA-01003: no statement parsed

Here is my code, the error comes only when I use pipes to add variables in my SQL

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;

If I replace the variable L_LOC_BLR with actual string it works fine. Can you please help me figure out the reason for this error.

Thanks

Answer 1

Most common reason - you forgot to quote your string literals.

You can easily investigate the problem single-handed - you should just log your SQL. Add dbms_output.put_line(l_sql_query) in your procedure, look at the output and try to execute it manually - you'll see the reason.

Anyway, your approach is bad design in most cases. You should use bind variables instead:

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 
     USING L_LOC_BLR, L_LOC_PUNE, L_LOC_MAS;

EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('ERROR SQL:' || L_SQL_QUERY);
  ROLLBACK;
  RETURN; 
END GET_EXP_INV_RECORD;