How to convert CLOB to BLOB

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.

Leave a Reply

Your email address will not be published. Required fields are marked *