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

Question

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 I 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?

Solution 1

I got this worked by using the option described in

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

Solution 2

Try to use DBMS_LOB.GETLENGTH(), instead of trimming the string

Solution 3

You can get around the buffer issue by reading the BLOB value in chunks. In order to do that, you can define a user defined function (UDF) as suggested here. The following UDF reads the BLOB field one chunk at a time, converts that to VARCHAR2 and appends the results consecutively to return the result as a CLOB:

CREATE OR REPLACE FUNCTION VC2CLOB_FROM_BLOB(B BLOB)
RETURN CLOB IS
    c CLOB;
    n NUMBER;
BEGIN
    IF (b IS NULL) THEN 
        RETURN NULL;
    END IF;
    IF (LENGTH(b) = 0) THEN
        RETURN EMPTY_CLOB();
    END IF;
    DBMS_LOB.CREATETEMPORARY(c, TRUE);
    n := 1;
    WHILE (n + 32767 <= LENGTH(b)) LOOP
        DBMS_LOB.WRITEAPPEND(c, 32767, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(b, 32767, n)));
        n := n + 32767;
    END LOOP;
    DBMS_LOB.WRITEAPPEND(c, LENGTH(b) - n + 1, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(b, LENGTH(b) - n + 1, n)));
    RETURN c;
END;
/

After having defined it, you can simply call it like so:

SELECT VC2CLOB_FROM_BLOB(variable_name);

Worked like a charm for my problem.

Comments

[jlSta]

Could you explain how to insert it?