Re: Help: Simple Dynamic SQL question

A copy of this was sent to "Araxes Tharsis" <pnrmaia_at_hotmail.com> (if that email address didn't require changing) On Sun, 27 May 2001 00:42:42 +0100, you 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
>

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 you were trying -- your system will spend 90% or more of its time parsing queries and you'll 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