Oracle PL/SQL Conversion of Oracle BLOB datatype to CLOB
The PL/SQL example shown below can be used to convert BLOB data into CLOB data using the Oracle package DBMS_LOB. The description data stored in the BLOB column (architecture_desc) in the lu_db_architecture table is converted to CLOB. The subfunction shown below can be customized and created as a main function depending on usage requirements.

DECLARE
  FUNCTION blob2clob (i_blob IN BLOB) RETURN CLOB AS
    o_clob CLOB;
    i_dest_offset PLS_INTEGER := 1;
    i_src_offset PLS_INTEGER := 1;
    i_lang_context PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
    i_Warning PLS_INTEGER;
BEGIN
  IF i_blob IS NOT NULL THEN
   IF LENGTH(i_blob) = 0 THEN
     RETURN EMPTY_CLOB();
   END IF;
   DBMS_LOB.createTemporary(lob_loc => o_clob,
     cache => TRUE); -- read into buffer cache
   DBMS_LOB.CONVERTTOCLOB(
     dest_lob => o_clob,
     src_blob => i_blob,
     amount => DBMS_LOB.LOBMAXSIZE,
     dest_offset => i_dest_Offset,
     src_offset => i_src_Offset,
     blob_csid => DBMS_LOB.DEFAULT_CSID,
     lang_context => i_lang_context,
     warning => i_Warning
   );
   ELSE
    o_clob := NULL;
   END IF;
   RETURN o_Clob;
  EXCEPTION
    WHEN OTHERS THEN RAISE;
  END blob2clob;
BEGIN
  FOR txt IN (SELECT architecture_desc arch_desc FROM lu_db_architecture) LOOP
     DBMS_OUTPUT.PUT_LINE(CHR(10)||' -> '||SUBSTR(blob2clob(txt.arch_desc),1,250));
     DBMS_OUTPUT.PUT_LINE(CHR(10)||' -> '||SUBSTR(blob2clob(txt.arch_desc),251));
  END LOOP;
END;
/



Back


Oracle registered trademark of Oracle Corporation
[ 82 ]