如何解决Oracle中去除XML字符串单引号时REPLACE报ORA-00907错误？

问题描述 #

从SOAP服务获取的XML字符串里，<Details>和<Location>标签内包含单引号（示例中为Ha'il），尝试用REPLACE函数去除单引号时，SQL语句无法解析，抛出错误：

[Error] Execution (45: 41): ORA-00907: missing right parenthesis

错误原因 #

Oracle以单引号作为字符串的边界标识，当字符串内部出现单个单引号时，数据库会误将其识别为字符串结束符，导致后续语法结构混乱，最终触发括号不匹配的错误。

解决方案 #

1. 手动传入字符串时：转义内部单引号 #

按照Oracle的规则，将原字符串中的单个单引号替换为两个连续单引号，再执行REPLACE函数。修正后的SQL语句如下：

select REPLACE('<Tracking diffgr:id="Tracking311" msdata:rowOrder="310">
                            <rowId>736729953</rowId>
                            <awbNo>290349641723</awbNo>
                            <Date>12 Oct 2022 13:32</Date>
                            <Activity>OUT FOR DELIVERY</Activity>
                            <Details>Ha''il</Details>
                            <Location>Ha''il</Location>
                            <scanCode>OFD</scanCode>
                        </Tracking>', chr(39), '') from dual

2. 从表字段读取XML时：直接调用REPLACE #

如果XML存储在数据库表的字段中，无需手动转义单引号，直接对字段执行REPLACE即可，数据库会自动识别字段内的单引号：

-- 假设表名为soap_tracking，XML字段名为tracking_data
select REPLACE(tracking_data, '''', '') from soap_tracking;

注：chr(39)和'''都代表单引号，两种写法效果一致，可根据习惯选择。

内容的提问来源于stack exchange，提问作者Adeel Aslam