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

Question

一些背景:数据库是甲骨文。我正在尝试为表格的每一行创建一个分隔字符串。分隔字符串的某些值必须来自 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

然后我尝试添加第 4 个元素(来自 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 为空且 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%#test

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

Answer 1

顺便说一下，您的错误原因是 xmltype 构造函数存在空 lob 缺陷。我个人总是使用 xmltype.createxml(item_txt) 代替。您会发现它同样有效，而不必担心 nvl 等问题。