Loading a file into a BLOB object in Oracle is easy. All we need to do is use the DBMS_LOB package to load the file into a temporary LOB object. This is demonstrated in the following example.
This example assumes that you have a directory object set up where the files are stored. In this example the following object is used:
CREATE DIRECTORY FILEUPLOADS AS '/home_new/dmt/public_html/fileuploads';
Use the following function to load a file based on the filename parameter and return the BLOB reference for processing:
CREATE OR REPLACE FUNCTION loadBlobFromFile(p_file_name VARCHAR2) RETURN BLOB AS dest_loc BLOB := empty_blob(); src_loc BFILE := BFILENAME('FILEUPLOADS', p_file_name); BEGIN -- Open source binary file from OS DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY); -- Create temporary LOB object DBMS_LOB.CREATETEMPORARY( lob_loc => dest_loc , cache => true , dur => dbms_lob.session ); -- Open temporary lob DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE); -- Load binary file into temporary LOB DBMS_LOB.LOADFROMFILE( dest_lob => dest_loc , src_lob => src_loc , amount => DBMS_LOB.getLength(src_loc)); -- Close lob objects DBMS_LOB.CLOSE(dest_loc); DBMS_LOB.CLOSE(src_loc); -- Return temporary LOB object RETURN dest_loc; END loadBlobFromFile; /
Easy as that.
8 replies on “Loading a File Into a BLOB Object in Oracle”
After reading it I got that it provides way for loading file into a BLOB object in oracle. Can you tell me other easiest way to do this?
It really depends on what your trying to do. If you can provide me with more details of your requirements then I may be able to help you out.
Thanks for the info. It really works please explain us how we work with clob data type and share with us some tips for adding a video or picture to our data base.
Works perfectly. Thanks and keep up the good work!
My file is attached but while opening file is corrupt. Please help me out
Can anyone help me with my requirement
1) I need to upload the file from apex , file browse button.
2) File need to store in the folder of our system not table or database.
3) I need to provide table of uploaded files with download button.
4) When i click download on respective file , that file only should be download.
Ex: i upload abc.png image, that is stored in upload folder. Now in apex front end i provide table with filename & download button beside it. When click on download button. abc.png only should down.
Note i gave example for one image. it can be any file extension
Thanks a lot!