Skip to Main Content
  • Questions

  • ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6843, maximum: 2000)

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, pushpendra.

Asked: April 10, 2019 - 7:23 am UTC

Last updated: April 17, 2019 - 9:22 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

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

and Chris said...

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>


We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.