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

sql oracle stored-procedures blob clob
kumarb picture kumarb · Aug 27, 2014 · Viewed 79.4k times · Source

I am attempting to read a blob message and display it as a variable in one of my procedures, but am getting the error below:

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

I have googled and found a suggestion to trim the blob message as below, but would rather display the complete blob message as a string. 

UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(variable_name,2000,1))

How can I display the complete message? Is there a setting on either the database or procedure level that I can change?

Answer

kumarb picture kumarb · Aug 27, 2014

I got this worked by using the option described in

http://www.dba-oracle.com/t_convert_blob_varchar_datatype.htm

Related questions

Searching for Text within Oracle Stored Procedures

I need to search through all of the stored procedures in an Oracle database using TOAD. I am looking for anywhere that the developers used MAX + 1 instead of the NEXTVAL on the sequence to get the next ID number. I've …

Read More
Inserting into Oracle and retrieving the generated sequence ID

I have a handful of raw SQL queries for SQL Server which use SCOPE_IDENTITY to retrieve the generated ID for a specific INSERT immediately after that INSERT occurs all in one execution… INSERT into Batch( BatchName, BatchType, Source, Area ) …

Read More
Paging with Oracle

I am not as familiar with Oracle as I would like to be. I have some 250k records, and I want to display them 100 per page. Currently I have one stored procedure which retrieves all quarter of a million records …

Read More