Converting text data from CLOB to BLOB can be tricky. Let’s explore how to make that conversion.
CREATE FUNCTION CLOB_TO_BLOB(
P_VALUE IN CLOB,
P_CHARSET_ID IN INTEGER DEFAULT DBMS_LOB.DEFAULT_CSID
)
RETURN BLOB
IS
v_result BLOB;
v_clob CLOB := CLOB_TO_BLOB.P_VALUE;
v_charset_id INTEGER := CLOB_TO_BLOB.P_CHARSET_ID;
v_dest_offset INTEGER := 1;
v_src_offset INTEGER := 1;
v_lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
v_warning INTEGER := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
BEGIN
DBMS_LOB.CREATETEMPORARY(LOB_LOC => v_result, CACHE => FALSE);
DBMS_LOB.CONVERTTOBLOB(dest_lob => v_result,
src_clob => v_clob,
amount => DBMS_LOB.LOBMAXSIZE,
dest_offset => v_dest_offset,
src_offset => v_src_offset,
blob_csid => v_charset_id,
lang_context => v_lang_context,
warning => v_warning);
RETURN v_result;
END CLOB_TO_BLOB;
DECLARE
V_CLOB CLOB := 'This is clob variable';
V_BLOB BLOB;
BEGIN
V_BLOB := CLOB_TO_BLOB(P_VALUE => V_CLOB,
P_CHARSET_ID => NLS_CHARSET_ID('UTF8'));
END;
I used the function with UTF-8 character set. Additionally, if you don’t utilize DBMS_LOB.CREATETEMPORARY you might run into the ORA-06502 PL/SQL: numeric or value error, especially when handling null values. I hope this post helps.