Re: ' in dynamic sql
From: gti_matt <gtimatt_at_home.com>
Date: Fri, 07 Dec 2001 03:33:13 GMT
Message-ID: <ZLWP7.49222$Sx.13270291_at_news1.elcjn1.sdca.home.com>
Date: Fri, 07 Dec 2001 03:33:13 GMT
Message-ID: <ZLWP7.49222$Sx.13270291_at_news1.elcjn1.sdca.home.com>
"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; /