CODEWITHSUNDEEP
sqloracle-database
Meraj Hussain
Meraj Hussain

Reputation: 362

Oracle: how to set a value in sql using variables in update sql statement

I am writing a procedure and I have requirement to run an update statement. Update sql will be prepared during the procedure execution in runtime.

  v_sql := 'UPDATE ' || tablename ||'SET' || columnname ||'=' || value;
    EXECUTE IMMEDIATE v_sql;

Please note that tablename, columname and value are variables. There is no problem wih tablename & columnname but I have issues with colmun value i.e the varibale 'value'. I am unable to set the value from update sql as mentioned above. It results in compilation errors or runtime errors. But if I hardcode as below it works.

 v_sql := 'UPDATE ' || tablename ||'SET' || columnname ||'= hardcodevalue';
        EXECUTE IMMEDIATE v_sql;

Could someone help me with this?

Upvotes: 0

Views: 683

Answers (3)

Koen Lostrie
Koen Lostrie

Reputation: 18650

While the other answers will work fine, you should try to avoid creating statements that are concatenated. This opens up your code for SQL Injection. You cannot bind table names and column names - in those cases contatenating is the only solution. But variables you should always bind:

v_sql := 'UPDATE ' || tablename ||' SET ' || columnname ||' = :val';
EXECUTE IMMEDIATE v_sql USING value;

To make it more complete, you could let oracle validate the statement for you before you execute it as explained in this asktom post

Upvotes: 0

Popeye
Popeye

Reputation: 35900

The issue with your code is the incorrect spacing and value must be wrapped in the single quotes. If your tablename, columnname and value are variables then what you need is as follows:

v_sql := 'UPDATE ' || tablename ||' SET ' || columnname ||' = ''' || value || '''';
    EXECUTE IMMEDIATE v_sql;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you run the code, it will probably generate something like this:

UPDATE tablenameSETcolumname=hardcodedvalue;

The spacing is not correct. You can try:

v_sql := 'UPDATE ' || tablename ||' SET ' || columnname || ' = hardcodevalue';

This will work when the hardcoded value is a number. I would suggest that you learn how to use EXECUTE IMMEDIATE with parameters so you can pass the hardcodedvalue as a parameter rather than munging the query string.

Upvotes: 0

Related Questions