HI iam using below query to read xml from blob and find the string but facing error
buffer to small ora-22835 blob to raw conversion (actual 15569,mximum 2000)
please help me out with below example
SELECT XMLTYPE (UTL_RAW.cast_to_varchar2 (TXT_RESPONSE_XML)).EXTRACT('<b:House>').Getstringval() XMLSrc
FROM a2009513_mma x WHERE x.num_quote= '0106800970294';
Getting below error
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.
Regards
Pushpendra
The value you're trying to convert to a varchar2 is too large!
Luckily you don't need to call UTL_RAW.cast_to_varchar2. XMLType has static function createXML which accepts CLOB/BLOB and returns an XMLType.
So you can use this instead:
create table t (
c1 blob
);
insert into t values (
utl_raw.cast_to_raw('<rt><a>this</a><b>that</b></rt>')
);
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')
<rt> <a>this</a>
<a>this</a>
<b>that</b>
</rt>