ORACLE : how to create dynamic update procedure depending on parameters?

Question

i want to make update procedure that updated the column of the table depends on parameter.

is this possible update the field depending on input parameter?

this is the example procedure that i've made:

CREATE OR REPLACE PROCEDURE procedure(parameter in varchar2)
IS errormessage varchar2(255);
  BEGIN
  UPDATE table
SET table.parameter = 'newvalue'
END;

and it doesn't works. please help.

Answer 1

Edit: Handled SQL Injection scenario.

You need execute immediate as parameter will be used as column name.

Note: Remember you are running update statement without where clause which will update all the rows.

Also you cannot pass a numeric column name as the value newvalue is a string. So If you want to handle it, then use if else condition and check for column data type before running update statement.

CREATE OR REPLACE PROCEDURE proc12(column_name in varchar2)
IS
v_count integer;
  BEGIN
  select count(*) into v_count from (select column_name as txt from dual) where regexp_like (txt,'[,|=|;]');
  if v_count =0 then
  execute immediate 'UPDATE tbl1 SET '||column_name||' = ''newvalue''';
  else
  dbms_output.put_line('SQL Injection detected. Exiting');
 end if;
END;




select * from tbl1;

+------+
| col1 |
+------+
| abc  |
| pqr  |
| xyz  |
+------+

call proc12('col1');

select * from tbl1;

+----------+
|   col1   |
+----------+
| newvalue |
| newvalue |
| newvalue |
+----------+

call proc12('balance=10000, col1');
SQL Injection detected. Exiting