ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion

I got “ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion ”  error in Oracle database.

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion

Details of error are as follows.

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: string, maximum: string)
 
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.

Buffer too small for CLOB to CHAR or BLOB to RAW conversion

This ORA-22835 errors are related with 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.
To solve this error, 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.

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.

Please make sure you are following the correct steps:

SQL> select segment_name, segment_type, initial_extent, next_extent, pct_increase, max_extents from user_segments;

|____________ Use this information in the next step :

SQL> select table_name, column_name from user_lobs where segment_name = '<LOBSEGMENT SEGMENT_NAME>';

|____________ Use this information in the next step :

SQL> alter table <TABLE_NAME> modify LOB (COLUMN_NAME) (storage (maxextents xxxx));

Make sure that when modifying characteristics of the LOB, you name the object with its logical name.

<<ORA-22853>> in most cases is returned by syntax issues.
   ---> Make sure there are no missing right or left parathesis '(' or ')'
Example:


Alter table ABC_TABLE modify LOB (ABC_LOB_COL) (storage (maxextents 2000));

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )