Blob to Clob using DBMS_LOB

Create function to convert the blob content to clob

CREATE OR REPLACE FUNCTION blob_to_clob (i_blob_content IN BLOB)
   RETURN CLOB
AS
   l_chunk         VARCHAR2 (32767);
   l_position      PLS_INTEGER := 1;
   l_buffer_size   PLS_INTEGER := 32767;
   l_clob_text     CLOB;
BEGIN
   -- Create temprary
   DBMS_LOB.createtemporary (l_clob_text, TRUE);
   FOR i IN 1 .. CEIL (DBMS_LOB.getlength (i_blob_content) / l_buffer_size)
   LOOP
      --convert to blob
      l_chunk :=
         UTL_RAW.cast_to_varchar2 (
            DBMS_LOB.SUBSTR (i_blob_content, l_buffer_size, l_position));
      -- append to clob variable
      DBMS_LOB.writeappend (l_clob_text, LENGTH (l_chunk), l_chunk);
      --Change start position
      l_position := l_position + l_buffer_size;
   END LOOP;
   RETURN l_clob_text;
END blob_to_clob;

Now try with Blob content to Clob conversion

DECLARE
   l_clob_text   CLOB;
   l_blob_content   BLOB;
BEGIN
   SELECT blob_content
     INTO l_blob_content
     FROM [TABLE_NAME]
    WHERE name = :p1_file_name;
   l_clob_text := blob_to_clob (l_blob_content);
END;