While I was working with a table containing a BLOB column:
SELECT id FROM table WHERE blob_column LIKE '%something%';
...I got the following error:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 16713, maximum: 4000)
An answer to this SO question addresses the problem with a conversion function which seemed like it would help me:
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;
/
However, trying this out:
SELECT id FROM table WHERE VC2CLOB_FROM_BLOB(blob_column) LIKE '%something%';
...I still got an error:
ORA-22921: length of input buffer is smaller than amount requested ORA-06512: at "SYS.DBMS_LOB", line 1163 ORA-06512: at "DATABASE.VC2CLOB_FROM_BLOB", line 18
What's going on and how to fix this?
You may wish to use DBMS_LOB.CONVERTTOCLOB
to do the conversion rather than iterating over substrings.
Inverting this answer, you can create a blob_to_clob
function:
CREATE OR REPLACE FUNCTION blob_to_clob(
value IN BLOB,
charset_id IN INTEGER DEFAULT DBMS_LOB.DEFAULT_CSID,
error_on_warning IN NUMBER DEFAULT 0
) RETURN CLOB
IS
result CLOB;
dest_offset INTEGER := 1;
src_offset INTEGER := 1;
lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
warning INTEGER;
warning_msg VARCHAR2(50);
BEGIN
IF value IS NULL THEN
RETURN NULL;
END IF;
DBMS_LOB.CreateTemporary(
lob_loc => result,
cache => TRUE
);
DBMS_LOB.CONVERTTOCLOB(
dest_lob => result,
src_blob => value,
amount => LENGTH( value ),
dest_offset => dest_offset,
src_offset => src_offset,
blob_csid => charset_id,
lang_context => lang_context,
warning => warning
);
IF warning != DBMS_LOB.NO_WARNING THEN
IF warning = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
warning_msg := 'Warning: Inconvertible character.';
ELSE
warning_msg := 'Warning: (' || warning || ') during BLOB conversion.';
END IF;
IF error_on_warning = 0 THEN
DBMS_OUTPUT.PUT_LINE( warning_msg );
ELSE
RAISE_APPLICATION_ERROR(
-20567, -- random value between -20000 and -20999
warning_msg
);
END IF;
END IF;
RETURN result;
END blob_to_clob;
/