我有以下 Oracle 查询,它从 clob 列 (mytable.personalization_data) 返回数据。如果 clob 值少于 4,00 个字符,它可以正常工作,但如果它更多,我会收到"ORA-01706:用户函数结果值太大"错误消息。
这是 sql:
select name_str, value_str, order_str
from
(
SELECT
XMLTYPE(EXTRACTVALUE(XMLTYPE(personalization_data), '/personalizations/personalization[1]/data')) persData
FROM my_table
),
XMLTable('/Accessories/Personalization/PersonalizationItems'
PASSING persData
COLUMNS
name_str varchar2(100) PATH 'DisplayName',
value_str varchar2(2000) PATH 'Value',
order_str varchar2(10) PATH 'SortOrder'
);
示例 XML:
<personalizations>
<personalization>
<data>
<![CDATA[
<Accessories>
<AccessoryId>1234567</AccessoryId>
<Personalization>
<PersonalizationItems>
<SortOrder>1</SortOrder>
<DisplayName>Last Name</DisplayName>
<Value>Veekoff</Value>
</PersonalizationItems>
<PersonalizationItems>
<SortOrder>2</SortOrder>
<DisplayName>First Name</DisplayName>
<Value>Ivana</Value>
</PersonalizationItems>
</Personalization>
</Accessories>
]]>
</data>
</personalization>
</personalizations>
有人可以提供建议吗?
所以... 问题是 EXTRACTVALUE 只返回VARCHAR2。并且您正在使用它来删除 <![CDATA[
data
节点中的内容,因此您可以再次将其解析为 XML。事实证明,Oracle允许您漂亮地打印XML或在SQL中就地更改clob的大多数方式都返回VARCHAR2。
我想我想出了一些应该有效的东西,使用 xpath 函数来摆脱 CDATA 包装器,并取消结果。这有点尴尬,我相信一定有更好的方法可以做到这一点,但我找不到它。
select name_str, value_str, order_str
from
(
SELECT XMLTYPE(DBMS_XMLGEN.CONVERT(
XMLQUERY('/personalizations/personalization[1]/data/substring(text(),9,string-length(text())-12)' passing XMLTYPE(personalization_data) returning content).getClobVal()
, 1)) persData
FROM my_table
),
XMLTable('/Accessories/Personalization/PersonalizationItems'
PASSING persData
COLUMNS
name_str varchar2(100) PATH 'DisplayName',
value_str varchar2(2000) PATH 'Value',
order_str varchar2(10) PATH 'SortOrder'
);
我想普通的substr
可以处理 clob,但这并没有太大的改进。
select name_str, value_str, order_str
from
(
SELECT XMLTYPE(substr( pData, 10, length(pData)-12)) persData
from (select
XMLQUERY('/personalizations/personalization[1]/data/text()' passing XMLTYPE(personalization_data) returning content).getClobVal() as pData
FROM my_table)
),
XMLTable('/Accessories/Personalization/PersonalizationItems'
PASSING persData
COLUMNS
name_str varchar2(100) PATH 'DisplayName',
value_str varchar2(2000) PATH 'Value',
order_str varchar2(10) PATH 'SortOrder'
);