CLOB to BLOB using PLSQL

Create function to convert the CLOB to BLOB

preview

CREATE OR REPLACE FUNCTION clob_to_blob (i_clob_text IN CLOB)
   RETURN BLOB
IS
   /*
   Version 1 29-mar-2020
   */
   l_blob_file     BLOB;
   l_lob_length    PLS_INTEGER := DBMS_LOB.getlength (i_clob_text);
   l_position      PLS_INTEGER := 1;
   l_buffer_size   RAW (32767);
BEGIN
   DBMS_LOB.createtemporary (l_blob_file, TRUE);
   DBMS_LOB.open (l_blob_file, DBMS_LOB.lob_readwrite);
   LOOP
      l_buffer_size :=
         UTL_RAW.cast_to_raw (
            DBMS_LOB.SUBSTR (i_clob_text, 16000, l_position));
      IF UTL_RAW.LENGTH (l_buffer_size) > 0
      THEN
         DBMS_LOB.writeappend (l_blob_file,
                               UTL_RAW.LENGTH (l_buffer_size),
                               l_buffer_size);
      END IF;
      l_position := l_position + 16000;
      EXIT WHEN l_position > l_lob_length;
   END LOOP;
   RETURN l_blob_file;
END clob_to_blob;

Now try with clob to blob conversion

DECLARE
   l_clob_text   CLOB;
   l_blob_file   BLOB;
   l_blob_2      BLOB;
BEGIN
   --- Getting Blob File
   SELECT blob_content
     INTO l_blob_file
     FROM apex_application_files
    WHERE filename = :file_name;
   -- Converting Blob to Clob
   l_clob_text := blob_to_clob (l_blob_file);
   -- Converting CLOB TO BLOB
   l_blob_2 := clob_to_blob (l_clob_text);
END;

You might Like


comments powered by Disqus