Update DB column that is specified at runtime

Question

Hi,

I'm trying to update a column in a database, but the column needs to be specified at runtime. For example, given the table:

| PKEY | ColumnA | ColumnB | ColumnC |

I want to write an stored procedure that will update a given column per PKEY. Such as:

updateColumn(pkey, columnName, Value);

This could easily be done using java/JDBC etc, but this needs to be a stored procedure.

also, its an Oracle database

Answer 1

You should look into dynamic SQL.

A good place to start is Execute Immediate and the Oralce Application Developer's Guide

Answer 2

Dynamic SQL is indeed your answer. You can build up an SQL statement and then execute it, making sure you take care to avoid SQL injection problems. Here's a rough outline of a procedure that should work in SQL Server. You'll need to re-target to Oracle. I've guessed at the types for your primary key and value.

create proc updateColumn (@pkey int, @columnName varchar(64), @value int)
as
declare @cmd varchar(512)
set @cmd = 'update YOUR_TABLE set ' + @columnName + ' = ' + str(@value) +
    ' where KEY = ' + str(@pkey)
exec (@cmd)

Answer 3

Converting dave's proc to Oracle PL/SQL with minimal changes:

create procedure updateColumn (pkey int, columnName varchar2, value int) as
begin
execute immediate 'update YOUR_TABLE set ' ||
    columnName || ' = ' || TO_CHAR(value) ||
    ' where KEY = ' || TO_CHAR(pkey);
end;

I would add a check to ensure that columnName is one of the columns in the table, to reduce the risk of SQL injection.