Re: ' in dynamic sql

"Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message news:3c0f8809.12667769_at_news...
> Allan Martin doodled thusly:
>
> >Hi,
> >
> >I have got a dynamic SQL statement which when build, the data which is going
> >to be inserted into a table, may have the ' symbol in it. This means that
> >when I'm executing my SQL statement, the string is ended by the rouge ' .
> >Can anyone suggest anything?

Either put the data in a variable first and/or escape it with additional single quotes. Example:

declare
  v_my_value constant varchar(40) := 'John''s stuff'   v_sql varchar(32767) := NULL;
begin

  v_sql := 'update my_table ' ||
           'set my_column = ' || ''' || v_my_value || '''
           'where some_value = || ''' || 'KEY_VALUE' || ''' || ';'

• process dynamic sql here end; /