Below I am giving a function example to get the BLOB from a file in PL/SQL.
PL/SQL Function to Get BLOB from a File
The following function will read a file and will return the BLOB data type of the file. The function GET_BLOB takes two parameters: (1) Oracle directory object name (2) file name.
CREATE OR REPLACE FUNCTION get_blob (i_dir IN VARCHAR2, i_file IN VARCHAR2)
RETURN BLOB
AS
l_bfile BFILE;
l_blob BLOB;
BEGIN
DBMS_LOB.createtemporary (l_blob, FALSE);
l_bfile := BFILENAME (i_dir, i_file);
DBMS_LOB.fileopen (l_bfile, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile (l_blob, l_bfile, DBMS_LOB.getlength (l_bfile));
DBMS_LOB.fileclose (l_bfile);
RETURN l_blob;
EXCEPTION
WHEN OTHERS
THEN
IF DBMS_LOB.fileisopen (l_bfile) = 1
THEN
DBMS_LOB.fileclose (l_bfile);
END IF;
DBMS_LOB.freetemporary (l_blob);
RAISE;
END get_blob;Test
Suppose you have a file myimage.jpg in C:\Pics folder, then create an Oracle directory object, for example, MY_PICS for folder C:\Pics and call the below function by passing the directory name MY_PICS and the file name myimage.jpg. If you don't know how to create a directory object in Oracle, check this link: Create Oracle Directory Object.
DECLARE
f_blob BLOB;
BEGIN
f_blob := get_blob ('MY_PICS', 'myimage.jpg');
END;Now you have the BLOB of file myimage.jpg in variable f_blob.
