Oracle - Dynamic SQL column name in Merge / insert statement

You should always be cautious while using dynamic SQL. Firstly, it is better to check if a static SQL statement is working fine and then try to convert it by modifying the dynamic parts. Also, a dbms_output before execute immediate helps you to know if the prepared sql is syntactically correct. Secondly, concatenating values is prone to SQL Injection and should be avoided.Preferred option is to use bind variables with the USING option of EXECUTE IMMEDIATE.

since p_planned_value is defined as a number, It implies that the datatype of all the columns you're planning to update/insert are going to be integers. I have used it accordingly in my example in the demo. If that's not the case, you will have to rethink how you're going to define the parameters of the procedure for it work for other cases like DATE datatypes.

CREATE OR REPLACE PROCEDURE insert_or_upd_movement_baselines_planned_weight_proc (
     p_id                 IN VARCHAR2,
     p_date               IN DATE,
     p_planned_col_name   IN VARCHAR2,
     p_planned_value      IN NUMBER
)
     AS
  plsql_block   VARCHAR2(4000);
     BEGIN
plsql_block := 'merge into MOVEMENT_BASELINES mb using 
 ( select :id as movement_id,:dt as movement_date from dual
  ) s ON ( mb.movement_id = s.movement_id  
              and mb.movement_date = s.movement_date )
     when matched then update set '
          || p_planned_col_name || ' = ' || p_planned_value || 
 ' when not matched then insert (MOVEMENT_ID, MOVEMENT_DATE,'
          || p_planned_col_name || ')
       values (:id,:dt,:value)';

EXECUTE IMMEDIATE plsql_block
              USING p_id,p_date,p_id,p_date,p_planned_value;

END insert_or_upd_movement_baselines_planned_weight_proc;
/

Demo

This part is definitely fishy:

|| p_date ||

since it actually does the same like

|| to_char(p_date) ||

so the unquoted value of the date becomes part of the statement, which will not result in a valid sql statement. Try this instead:

   values ( ' || p_id || ', to_date(''' || to_char(p_date) || '''), ' || p_planned_value || ')

This is an addendum to Kaushik's answer, where they state (quite correctly, if not in so many words) that your statement is completely vulnerable to SQL injection.

I would write your procedure as follows:

CREATE OR REPLACE PROCEDURE insert_or_upd_movement_baselines_planned_weight_proc(p_id               IN VARCHAR2,
                                                                                 p_date             IN DATE,
                                                                                 p_planned_col_name IN VARCHAR2,
                                                                                 p_planned_value    IN NUMBER) AS
  v_sql              CLOB;
  v_planned_col_name VARCHAR2(32);
BEGIN
  v_planned_col_name := dbms_assert.simple_sql_name(p_planned_col_name);

  v_sql := 'MERGE INTO movement_baselines tgt'||CHR(10)||
           'USING (SELECT :p_id movement_id,'||CHR(10)||
           '              :p_date movement_date,'||CHR(10)||
           '              :p_planned_value planned_value'||CHR(10)||
           '       FROM   dual) src'||CHR(10)||
           'ON (tgt.movement_id = src.movement_id AND tgt.movement_date = src.movement_date)'||CHR(10)||
           'WHEN NOT MATCHED THEN'||CHR(10)||
           '  INSERT (tgt.movement_id, tgt.movement_date, tgt.'||v_planned_col_name||')'||CHR(10)||
           '  VALUES (src.movement_id, src.movement_date, src.movement_date)'||CHR(10)||
           'WHEN MATCHED THEN'||CHR(10)||
           '  UPDATE'||CHR(10)||
           '  SET    tgt.'||v_planned_col_name||' = src.planned_value';


  dbms_output.put_line('merge statement: ' || chr(10) || v_sql);

  EXECUTE IMMEDIATE v_sql
    USING p_id, p_date, p_planned_value;

END;
/

Note the use of dbms_assert to sanitize your input - in this instance, we are checking that the value you passed in to p_planned_col_name meets the requirements for it to be a valid identifier, which means it definitely can't be used for SQL injection.

In addition, I moved the parameters into a subquery, meaning that the using clause of the execute immediate is now shorter and, I think, clearer and easier to maintain.