oracle报错，XML节点值超过varchar(4000)截取方法。ORA-06502，ORA-06512，ORA-01706

使用extract().getstringval()获取节点文本

sql如下：

select extract(XMLTYPE(b.XMLVALUE), '//姓名//child::text()').getstringval()
  from tab b

1
2
3

由于我这里的XMLVALUE是clob类型，所以先XMLTYPE(b.XMLVALUE) 是把XMLVALUE字段转为XMLTYPE类型。然后再通过extract().getstringval()获取节点文本，其中extract()得到的还是XMLTYPE类型，getstringval()则返回varchar。但是，如果内容不超过了4000，就会报错：

ORA-06502: PL/SQL: 数字或值错误 : character string buffer too small
ORA-06512: 在 "SYS.XMLTYPE", line 169
查看错误堆栈的程序源？
1
2
3

使用extractValue获取文本

使用extractValue的话，就可以一步，之前获取到节点的文本值，但是这个节点必须是末端的节点，否则就会报错。
sql如下：

select extractValue(XMLTYPE(b.XMLVALUE), '/EMRDoc') from tab b;
1

报错：
ORA-19025: EXTRACTVALUE 只返回一个节点的值。

正常使用extractValue获取节点值

select extractValue(XMLTYPE(b.XMLVALUE), '/EMRDoc/xm') from tab b;
1

如果内容不超过了4000，同样也会报错：
ORA-01706: 用户函数的结果值过大

使用XMLTABLE获取节点值

XMLTABLE()可以直接返回一个表，里面节点获取的值可以直接指定字段类型，所以就可以用clob装下超过4000文本。

select (select NVL(dbms_lob.substr(text, 2000, 1), '99#')
          from XMLTABLE('/EMRDoc/xm' PASSING XMLTYPE(b.XMLVALUE) COLUMNS text clob PATH
                        '/EMRDoc/xm'))
  from tab b

1
2
3
4
5

如果需要同时获取多列，则

select t.*,b.xm,b1.xb,b1.nl,NVL(dbms_lob.substr(b.xm, 2000, 1), '99#')
  from tab b,
       XMLTABLE('/EMRDoc' PASSING XMLTYPE(b.XMLVALUE) COLUMNS xm clob PATH
                '/EMRDoc/xm',
                xb clob  PATH '/EMRDoc/xb',
                nl varchar(4000) PATH '/EMRDoc/nl') b1
1
2
3
4
5
6

效果如下：