Oracle UTL_FILE file corruption

96 views Asked by At

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

We have the below procedure that reads blob data ( docx file) and writes to a directory using UTL_FILE. The file is correctly stored in the blob column and we are able to download and open the file.

However when we try to open the file that was written to the directory, the file is corrupted.

Below is the code snippet used to write the file. We are unable to determine whu UTL_FILE is causing the file corruption. Has anyone faced a similar issue writing docx files ?

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_blob      BLOB;
  l_blob_len  INTEGER;
BEGIN
  
  SELECT blob_file
  INTO   l_blob
  FROM   tab1
  WHERE  id=1;

  l_blob_len := DBMS_LOB.getlength(l_blob);
  
  l_file := UTL_FILE.fopen('DB_DIR','file.docx','wb', 32767);

  
  WHILE l_pos < l_blob_len LOOP
    DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
    UTL_FILE.put_raw(file      => l_file      ,
                                buffer    => l_buffer,
                                autoflush => true);
    l_pos := l_pos + l_amount;
  END LOOP;

  UTL_FILE.fclose(l_file);
  
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

Appreciate any help in this regard

Thanks in advance

0

There are 0 answers