Oracle ORA-22835: 缓冲区太小，无法CHAR或BLOB转换为RAW (实际: 6843，最大值: 2000)

嗨，iam使用下面的查询从blob读取xml并找到字符串，但面临错误
缓冲区到小ora-22835 blob到原始转换 (实际15569，最大2000)
请用下面的例子来帮助我

 SELECT XMLTYPE (UTL_RAW.cast_to_varchar2 (TXT_RESPONSE_XML)).EXTRACT('').Getstringval() XMLSrc 
  FROM a2009513_mma x WHERE x.num_quote= '0106800970294';
复制

获取低于错误

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6843, maximum: 2000)
22835. 00000 -  "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"
*Cause:    An attempt was made to convert CLOB to CHAR or BLOB to RAW, where
           the LOB size was bigger than the buffer limit for CHAR and RAW
           types.
           Note that widths are reported in characters if character length
           semantics are in effect for the column, otherwise widths are
           reported in bytes.
*Action:   Do one of the following
           1. Make the LOB smaller before performing the conversion,
           for example, by using SUBSTR on CLOB
           2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.
复制

问候
普什彭德拉

你试图转换为varchar2的值太大了!

幸运的是，您不需要调用UTL_RAW.cast_to_varchar2。XMLType具有静态函数createXML，它接受CLOB/BLOB并返回XMLType。

所以你可以用这个代替:

create table t (
  c1 blob
);

insert into t values ( 
  utl_raw.cast_to_raw('thisthat') 
);

commit;

select xmltype.createXML ( c1, 1, null ),
       xmltype.createXML ( c1, 1, null ).extract ( '/rt/a' )
from   t;

XMLTYPE.CREATEXML(C1,1,NULL)              XMLTYPE.CREATEXML(C1,1,NULL).EXTRACT('/RT/A')   
                                      this 
  this
  that
复制