ora-22835 buffer too small for clob to char or blob to raw conversion

Posted on 2009-04-24
Last Modified: 2013-12-18
I am querying a view in Oracle 10g and received this error below:
ora-22835 buffer too small for clob to char or blob to raw conversion

The problematic field datatype is blob and I need to convert it to text to UNION several tables. I tried using DBMS_LOB.SUBSTR but still getting the same error. Any idea?
Question by:burgomaster
    LVL 40

    Expert Comment

    What size are you passing to DBMS_LOB.SUBSTR? Try something really small to verify.
    LVL 4

    Accepted Solution

    This is expected behavior becuase it is an unpublished Bug
    in 10.1 (and 9i) due to which the conversion was truncated without warning or error.

    However, in 10.2 this bug was fixed and so the truncation does not occur but also the varchar can
    only hold 4000 bytes. In this case the clob is 5608 bytes and so the TO_CHAR cannot be performed and the error is expected.

    Use DBMS_LOB.SUBSTR instead of TO_CHAR.
    The DBMS_LOB.SUBSTR function returns a VARCHAR just like TO_CHAR, and you can limit the amount of data that you want to convert.

    LVL 4

    Expert Comment

    I just double checked...
    This issue is fixed in (Server Patch Set) and (Base Release)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now