create or replace function blob_to_varchar(tempblob in Blob) return varchar2 is
  l_amount      BINARY_INTEGER := 800;
  l_offset      INTEGER := 1;
  l_buffer      RAW(32767);
  l_text_buffer VARCHAR2(32767);
BEGIN
  IF (tempblob IS NULL) THEN
    return '';
  end if;
  DBMS_LOB.READ(tempblob, l_amount, l_offset, l_buffer);
  l_text_buffer := UTL_RAW.CAST_TO_VARCHAR2(l_buffer);
  return l_text_buffer;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('!ERROR: ' || SUBSTR(SQLERRM, 1, 247));
    RAISE;
END blob_to_varchar;