How i can pass column names from variables in plsql update statement

Question

DECLARE

  v_name  A.TRANSACTION_TYPE%TYPE   :='SALARY';
  v_salary    A.SALARY%TYPE := 1000;

BEGIN

  update A set v_name= v_salary where EMPID = 517; 
-- PL/SQL: ORA-00904: "v_name": invalid identifier
--update A set SALARY = 1000 where EMPID = 517;

END;

/

My idea is to update table columns , but these column names are stored in variable. Is there any way to pass column names from variable ? Is there any options apart from Execute Immediate

Answer 1

Not sure if this will work in your situation, but I've written solutions where I wrote a script in SQLPlus and it "wrote" (using dbms_output.put_line or even just prompt) another script that did queries, and the columns/tables in those queries was determined by the logic in the SQLPlus script. Then I would execute as a script the output from my first script, and it would execute dynamically generated queries without ever needing execute immediate.

Answer 2

The following idea may work for multiple columns that are typed the same... As written, it will update all columns every time for a given record, but only the column specified by v_name will be changed to the value set in v_value; the other columns are simply updated to their existing value. The idea can be played with using DECODE, NVL or other similar conditional operators.

 declare

 v_name varchar2(20):= 'SAL';
 v_value emptest.sal%TYPE := 5000;

 begin

 update emptest
 set sal = ( select case when v_name = 'SAL' then v_value else sal end from dual),
 comm = ( select case when v_name = 'COMM' then v_value else comm end from dual)
 where empno = 7369;

 commit;

 end;