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

Question

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

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

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

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

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

Answer 1

I succeeded using the option described in

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

Answer 2

Try using DBMS_LOB.GETLENGTH () instead of trimming a row