Re: Help: Simple Dynamic SQL question

A copy of this was sent to "Daniel A. Morgan" <dmorgan_at_exesolutions.com> (if that email address didn't require changing) On Sat, 26 May 2001 19:36:34 -0700, you wrote:

>Araxes Tharsis wrote:
>
>> I want to create a statement like the following, but how do I put the '
>> symbol around the variable "value" (because it is a text variable) ?!?!
>>
>> EXECUTE IMMEDIATE 'UPDATE ' || table_name || ' SET ' || field_value || ' = '
>> || value || ' WHERE ' || id || ' = ' || p_id;
>>
>> Thanks,
>> Araxes
>
>You don't because you don't need to.
>
>Daniel A. Morgan

well, as they said value is a TEXT string so if they did it that way, they most certainly would need to quote it. Else the statment would end up being something like:

   update EMP set ename = KING where id = 5555;

that wouldn't work. the right answer is please use bind variables!!!!

execute immediate 'update ' || table_name ||
                    ' set ' || field_value || ' = :x 
                    where ' || id || ' = :y' using value, p_id;

Never hard code literals into DML like they were trying -- the system will spend 90% or more of its time parsing queries and it kill any chance of scaling up your system. Use Bind variables (removes the "quote" issues as well).

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp