BLOB Load File Into BLOB Oracle PL/SQL

Loading a File Into a BLOB Object in Oracle

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 Comments

  1. Hi,
    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?

  2. Lorrie,
    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.

    Cheers,
    Ariel

  3. Perfect! Working.

  4. 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.

  5. Works perfectly. Thanks and keep up the good work!

  6. SAMARPIT MAHESHWARI

    My file is attached but while opening file is corrupt. Please help me out

  7. hello all,

    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

    Thank you.

  8. Thanks a lot!

Leave a Comment

Your email address will not be published. Required fields are marked *

*