How to Get File From BLOB in Oracle?

How to Get File From BLOB in Oracle?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 2 mins read

In this tutorial, you will learn how to get a file from BLOB column in Oracle. To give an example, I have created a table EXT_FILES in Oracle and following is the structure of the table.

CREATE TABLE EXT_FILES
(
FILE_NAME VARCHAR2(1000 BYTE),
FILE_CONTENT BLOB
)
/

Insert the data into the above table. If you don't know how to insert a file into BLOB column, check the following article How To Insert A File in Oracle Table BLOB Column?

You must have an Oracle directory object created in your schema, to get the files from BLOB column to the disk. To create a directory in Oracle give the following command.

CREATE OR REPLACE DIRECTORY IMGDIR AS 'C:\TEMP\IMAGES';

The below is the Oracle Stored Procedure example, which will fetch the records from EXT_FILES table (containing BLOB data) using a cursor and then will extract the files one by one into the specified directory.

Oracle Stored Procedure Example To Get the File From BLOB Column in Oracle

CREATE OR REPLACE PROCEDURE get_files_from_blob
IS
l_file UTL_FILE.file_type;
l_buffer RAW (32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob_len INTEGER;
l_blob BLOB;
l_filename ext_files.file_name%TYPE;
/* cursor to get the records from the table */
CURSOR c_files
IS
SELECT file_name, file_content FROM ext_files;
BEGIN
OPEN c_files;

LOOP

FETCH c_files
INTO l_filename, l_blob;

EXIT WHEN c_files%NOTFOUND;

l_blob_len := DBMS_LOB.getlength (l_blob);

/* Change the directory name (IMGDIR) to your Oracle directory object name*/
l_file :=
UTL_FILE.fopen ('IMGDIR',
l_filename,
'wb',
32767);
l_pos := 1;

WHILE l_pos < l_blob_len
LOOP
DBMS_LOB.read (l_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);
END LOOP;

CLOSE c_files;
EXCEPTION
WHEN OTHERS
THEN
IF UTL_FILE.is_open (l_file)
THEN
UTL_FILE.fclose (l_file);
END IF;

RAISE;
END;

Test

BEGIN
get_files_from_blob;
END;
/

Output

PL/SQL procedure successfully completed.

You can now check the location of IMGDIR directory object for the extracted files.

See also: