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.

ShareShare on FacebookTweet about this on TwitterShare on Google+Share on LinkedInEmail this to someone

You may also like...

5 Responses

  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. admin says:

    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. Oliver says:

    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. Hei Jude says:

    Works perfectly. Thanks and keep up the good work!

Leave a Reply

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