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 )

 

 1 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

IT Tutorial