从 CLOB（包含 XML）中提取特定值，同时为表的每一行创建一个分隔字符串。某些行上的 CLOB 值可能为空

一些背景： 数据库是Oracle。我正在尝试为表的每一行创建一个分隔字符串。分隔字符串的某些值必须来自 CLOB（包含 XML）。某些行上的 CLOB 可能为空，这就是问题所在。

例如，我有一个表：“Item”，其中包含以下行：“Item_ID”、“Item_CD”、“Item_TXT”(CLOB)。该表有两行。一行将以下 XML 存储在“Item_TXT”中，另一行的“Item_TXT”为空。

<OuterTag>      
    <InnerTag>test</InnerTag>   
</OuterTag>     

我创建了以下 SQL 来返回分隔字符串的前 3 个元素：

SELECT 'Item%#'|| Item_ID ||'%#'|| Item_STAT_CD 
FROM Item; 

这成功了：

物品%#12345%#A

物品%#123456%#A

然后我尝试添加第四个元素（来自 CLOB 的值）。

SELECT 'Item%#'|| Item_ID ||'%#'|| Item_STAT_CD
EXTRACT(XMLTYPE(Item_TXT), '//OuterTag/InnerTag/text()').getStringVal()
FROM Item; 

此操作失败并出现以下错误：

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

我已将此问题缩小到有时 Item_TXT 为 null 并且 XMLTYPE() 无法处理此问题。为了证明这一点，我运行了以下命令：

SELECT 'Item%#'|| Item_ID ||'%#'|| Item_STAT_CD
EXTRACT(XMLTYPE(Item_TXT), '//OuterTag/InnerTag/text()').getStringVal()
FROM Item
WHERE Item_TXT IS NOT NULL; 

这成功了：

项目%#12345%#A%#测试

有没有办法处理整个表，从 clob 中提取值（如果可用），否则向分隔字符串添加 null/nothing ？

顺便说一句，你的错误的原因是xmltype构造函数存在空 lob 缺陷。我个人总是使用xmltype.createxml(item_txt)反而。你会发现它同样有效，无需担心nvl etc.