Problem with Execute immedite

Question

Hi All,

I am trying to run this code below using Execute Immediate but its not working and values are correct.

Please suggest the correct code.

declare
v_count number:=1;
v_conc_name varchar2(400);
v_val VARCHAR2(20):='vineet';
BEGIN 
v_conc_name:='INT_AP_PAY_CONV';
EXECUTE IMMEDIATE 'UPDATE xxx_cnc_prg_details
               SET
               PARAMETER'||v_count||'='
               ||v_val||' where CONCURRENT_PROGRAM_SHORT_NAME= '||v_conc_name;

end;  

Answer 1

Seems you missed quotes:

should be:

 ||' where CONCURRENT_PROGRAM_SHORT_NAME= '''||v_conc_name || '''';

because v_conc_name is varchar2

Answer 2

It's a lot easier (and better practice) to use bind variables:

declare
v_count number:=1;
v_conc_name varchar2(400);
v_val VARCHAR2(20):='vineet';
BEGIN 
v_conc_name:='INT_AP_PAY_CONV';
EXECUTE IMMEDIATE 'UPDATE xxx_cnc_prg_details
               SET
               PARAMETER'||v_count||'=:bv_val
               where CONCURRENT_PROGRAM_SHORT_NAME=:bv_conc_name'
  USING v_val, v_conc_name;

end;  

It's also a handy practice to use a string variable to hold the SQL. Then you can use DBMS_OUTPUT.PUT_LINE to view and verify it:

declare
v_count number:=1;
v_conc_name varchar2(400);
v_val VARCHAR2(20):='vineet';
v_sql LONG;
BEGIN 
v_conc_name:='INT_AP_PAY_CONV';
v_sql := 'UPDATE xxx_cnc_prg_details
               SET
               PARAMETER'||v_count||'=:bv_val
               where CONCURRENT_PROGRAM_SHORT_NAME=:bv_conc_name';
EXECUTE IMMEDIATE v_sql USING v_val, v_conc_name;

end; 

Answer 3

I would do like this:

DECLARE
  c           CONSTANT CHAR := '''';
  v_conc_name VARCHAR2(400) := 'INT_AP_PAY_CONV';
  v_val       VARCHAR2(20)  := 'vineet';
  v_count     NUMBER        := 1;
BEGIN
  EXECUTE IMMEDIATE 'UPDATE xxx_cnc_prg_details
                     SET
                     PARAMETER'||v_count||' = '||c||v_val||c||'
                     where CONCURRENT_PROGRAM_SHORT_NAME = '||c||v_conc_name||c;

END;