Has anyone run across this problem and found a good solution?
Have a request type command that runs a sql script. One of the paramenters passed is a request text field. If the field contains any quotes, such as in the phrase John's project then sqlplus gets confused.
With the Release 10g onwards, a new quoting mechanism is provided in the form of "q". This new quote operator allows us to choose our own quotation mark delimiter. It could look something like this:
select (select CUS_FIRST_TOKEN(q'+[REQD.VP.ORIG_FIELD]+') from dual),(select CUS_FIRST_TOKEN(q'+[REQD.VP.ORIG_FIELD]+') from dual) from dual
This will escape the quote by adding another quote.
The rule will then work.
The fairly bad news is that this operator escapes the issue but if you have rules copying field values from source to destination fields, it will also add the quote to the destination field value, meaning that if you have Source-Field value ‘D’Amacourt’, you’ll have Destination-Field value ‘D’’Amacourt’.
Thus, if you plan or actually already DO use this field as a source for yet another field, then there might be issues with THAT field. It is therefore recommended to test this operator excessively on any test-environment prior to going live with it.
you are correct. The problem is that SQL believes that the text that it's parsing, when including an additional quote, needs to be truncated as the parameter closes the text. The problem is that the text values are 'interpreted instead of just read. The q-Operator has helped in the past. Just an idea :-)
An alternative would be to use 'replace' in the UI-rule