转义字符用于Oracle的Xmltable

Question

我使用Xmltable将逗号分隔的电子邮件地址字段转换为值表。

WITH

data AS
  (
    select  1 ID, 'foo&bar@domain.tld,bar@domain.tld' recipients from dual
  )

select  ID, trim(COLUMN_VALUE) recipient
from    data,xmltable(('"'|| REPLACE( recipients , ',', '","') || '"'))

产生一个错误：

72000 ORA-19112:在计算过程中引发的错误: XVM-01003：“foo”1 "foo&bar@domain.tld“、"bar@domain.tld”-^

但是，当我将&替换为其实体值(&)时：

WITH

DATA AS
  (
    select  1 ID, 'foo&bar@domain.tld,bar@domain.tld' recipients from dual
  )

select  ID
        -- & --> &
        , replace( trim(COLUMN_VALUE), '&', '&') recipient
from    data
        -- & --> &
        ,xmltable(('"'|| REPLACE( replace( recipients, '&','&')  , ',', '","') || '"'))

查询工作如下：

ID,RECIPIENT
1,foo&bar@domain.tld
1,bar@domain.tld

我想象可能有其他字符在一个电子邮件地址中是有效的，但对Xmltable来说是有问题的。

有更好的方法吗？

Answer 1

你可以用 function

with data (id, recipients) as
(
  select 1, 'foo&bar@domain.tld,bar@domain.tld' from dual
)
select d.id, dbms_xmlgen.convert(x.column_value.getstringval(), 1) as recipient
from data d
cross join
xmltable(('"' || replace(dbms_xmlgen.convert(d.recipients, 0), ',', '","') || '"')) x

        ID RECIPIENT                     
---------- ------------------------------
         1 foo&bar@domain.tld            
         1 bar@domain.tld                

内部调用dbms_xmlgen.convert(d.recipients, 0)给您

foo&bar@domain.tld,bar@domain.tld

在将其修改为在每个逗号分隔的值周围有双引号并被拆分为多个行之后，您将以column_value作为结束：

foo&bar@domain.tld
bar@domain.tld

因此，外部dbms_xmlgen.convert(x.column_value.getstringval(), 1)将任何编码实体转换回它们的普通版本。

如果在PL/SQL上下文中这样做，那么可以使用dbms_xmlgen.entity_encode和dbms_xmlgen.entity_decode来代替固定的0和1，但它们在普通SQL中是不可用的。

(无论如何，only five entities是要担心的，但您最好还是允许它们全部使用--不管它们在电子邮件地址中是否有效--使用这样的函数调用可能不会让未来的维护人员感到困惑.)