Here is an example of Oracle PL/SQL stored procedure to download BLOB file to disk.
Oracle PL/SQL Stored Procedure Example to Download BLOB as File
The following PL/SQL procedure takes three arguments. First, Oracle Directory object name, second, file name, and the third is the BLOB. So you can pass these three values to it to download the BLOB as a file to the disk.
CREATE OR REPLACE PROCEDURE blob_to_file (i_dir IN VARCHAR2, i_file_name IN VARCHAR2, i_blob IN BLOB) AS l_file UTL_FILE.file_type; l_buffer RAW (32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_blob_len INTEGER; BEGIN l_blob_len := DBMS_LOB.getlength (i_blob); l_file := UTL_FILE.fopen (i_dir, i_file_name, 'WB', 32767); WHILE l_pos < l_blob_len LOOP DBMS_LOB.read (i_blob, l_amount, l_pos, l_buffer); UTL_FILE.put_raw (l_file, l_buffer, 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; END blob_to_file;
Below is an example of the usage of the above PL/SQL procedure.
To store a file to the disk, you need an Oracle directory object referring to the file system path. So first you create a directory object as following:
Create or Replace directory MY_DIR as 'd:\app_files\';
For Linux/Unix use the following path syntax:
Create or Replace directory MY_DIR as '/usr/ora1/myfiles/';
Now you can fetch the BLOB from the database and write it to the file system folder. Below is an example:
Declare Cursor c_emp_files is select empno, emp_file from emp_files; Begin -- it will loop the whole emp_files table and will download the emp_file (BLOB) as file for c in c_emp_files loop blob_to_file('MY_DIR', 'F'||c.empno||'.pdf', c.emp_file); end loop; End;