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