Thunderstone Support Forums

basically i am trying to assign a string to a variable which has some instances of " double quotes and ' single quotes.
But once " is encountered as end of the string and the error which is thrown up is
Missing start single quote in value
(that part of the string which i think the error is referring to is ,'"', ' ')

its part of plsql replace function . How do i escape double quotes

its something lijke replace(replace(replace(replace(replace((p.Expert_Brief_Desc || ' ' || h.p_present_role || ' ' || h.p_interest|| ' ' || h.p_past_role|| ' ' || h.p_education || ' ' || h.p_place_visited || ' ' || h.p_hobbies),'"', ' '), ' '' ', ' '), '|', ' '), chr(13), ' '), chr(10), ' ') as EXPERT

There are a couple of ways you can do it. Given that the string looks pretty static you might want to use something like:

<capture>replace(replace(replace(replace(replace((p.Expert_Brief_Desc || ' ' || h.p_present_role || ' ' || h.p_interest|| ' ' || h.p_past_role|| ' ' || h.p_education || ' ' || h.p_place_visited || ' ' || h.p_hobbies),'"', ' '), ' '' ', ' '), '|', ' '), chr(13), ' '), chr(10), ' ') as EXPERT</capture>
<$var=$ret>

The other option if you are pulling the data into Vortex would be to do the replace in Vortex, it looks like you are doing something like:

<sandr "[\x0a\x0d\x22\x27|]" " " $EXPERT>
<$EXPERT=$ret>

i liked the capture option
the string is dynamic. though the static part of the string (containing replace) i can capture it with the help of <capture> command.
The issue here is " is captured as " . The string i am forming would be sent to a function which in turn will execute the oracle query.
so i want " as it is ... and shouldnt appear as "

If you're not using any vortex variables you can use <verb noesc>...</verb> within the capture to prevent the html escapement. Otherwise you can use <vxcp htmlmode off>...<vxcp htmlmode on> around that section.

i am trying this
<capture>
<vxcp htmlmode off>
replace(replace(replace(replace(replace((p.Expert_Brief_Desc || ' ' || h.p_present_role || ' ' || h.p_interest|| ' ' || h.p_past_role|| ' ' || h.p_education || ' ' || h.p_place_visited || ' ' || h.p_hobbies),'"', ' '), ' '' ', ' '), '|', ' '), chr(13), ' '), chr(10), ' ') as EXPERT FROM t_person p, t_person_property h where p.person_id = h.person_id(+)
</vxcp htmlmode on>
</capture>
I am still getting "

am i doing anything wrong
</capture>

sorry its <vxcp htmlmode on> and not </vxcp htmlmode on>

Are you sure that it isn't your debug that is escaping, and the variable actually has the double quote properly? You might want to run the script as

texis script/main.txt

if it is not a web based execution. That will eliminate all HTML escapements (similar to putting <vxcp htmlmode off> at the entry point).

yeah thats how i am running the script


-bash-3.00$ texis build='update' migrationProfileTable_test
replace(replace(replace(replace(replace((p.Expert_Brief_Desc || ' ' || h.p_present_role || ' ' || h.p_interest|| ' ' || h.p_past_role|| ' ' || h.p_education || ' ' || h.p_place_visited || ' ' || h.p_hobbies),'"', ' '), ' '' ', ' '), '|', ' '), chr(13), ' '), chr(10), ' ') as EXPERT FROM t_person p, t_person_property h where p.person_id = h.person_id(+)


that is the o/p

notice the &quot above