Here I am giving an example to save BLOB data as a file in PL/SQL. BLOB data you can get it from the table having BLOB column, or you can get it from a file on the disk.
PL/SQL Function Example to Save BLOB as a File
The below procedure takes the following three arguments:
- Oracle Directory Object name (as i_dir).
- Save as filename (as i_file).
- BLOB data (as i_blob).
CREATE OR REPLACE PROCEDURE blob_to_file (i_dir IN VARCHAR2, i_file 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, '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; RAISE; END blob_to_file;
The following PL/SQL block will call the above procedure by passing the Oracle directory object, filename and BLOB data. For the BLOB data, I am using the GET_BLOB function for which I have given an example in my previous blog post: Get BLOB from a File in PL/SQL. In the below case, it will get the BLOB data from a file myfile.jpg from MY_DIR location and will save it to file abc.jpg in IMG_DIR location.
DECLARE f_blob BLOB; BEGIN /* check the above mentioned link for get_blob function example */ f_blob := get_blob ('MY_DIR', 'myfile.jpg'); /* now pass the blob to blob_to_file procedure to save it as a file */ blob_to_file ('IMG_DIR', 'abc.jpg', f_blob); END;
Now you can check the location of IMG_DIR directory for the file created through BLOB.
- How to Create an Oracle Directory Object?
- Learn how to Extract BLOB data from Oracle Table using Toad