web analytics
Home > Database | Oracle > Loading files into a BLOB column using Oracle External LOB BFILE Data type

Loading files into a BLOB column using Oracle External LOB BFILE Data type

I have a list of filenames and the corresponding files. I need to insert them into a table (filename, blobfile).

Make sure the user has execute privileges on UTL_FILE and DBMS_LOB
Put the files into an Oracle directory accessable by the oracle user

CREATE TABLE filenametab (filename VARCHAR2(100));
INSERT INTO filenametab values ('a.pdf');
INSERT INTO filenametab values ('b.pdf');

CREATE TABLE blobtab (filename VARCHAR2(100), blobfile BLOB);

CREATE OR REPLACE PROCEDURE LOAD_BLOBS (dir VARCHAR2)
IS
-- Declare section
  CURSOR ft IS 
  SELECT *
  FROM filenametab;
  
  PROCEDURE ins_blob (ft filenametab%ROWTYPE)
  AS
      lBlob  BLOB;
      lFile  BFILE := BFILENAME(dir, ft.filename);
  BEGIN 
      INSERT INTO blobtab (filename
                          ,blobfile)
      VALUES (ft.filename
             ,empty_blob())
      RETURNING blobfile INTO lBlob;
      DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);
      DBMS_LOB.OPEN(lBlob, DBMS_LOB.LOB_READWRITE);
      DBMS_LOB.LOADFROMFILE(DEST_LOB => lBlob
                           ,SRC_LOB  => lFile
                           ,AMOUNT   => DBMS_LOB.GETLENGTH(lFile));
      DBMS_LOB.CLOSE(lFile);
      DBMS_LOB.CLOSE(lFile);
      DBMS_LOB.CLOSE(lBlob);
      COMMIT;
  END;
  
-- Main section  
BEGIN
  FOR c1 IN ft
  LOOP
    ins_blob(c1);
  END LOOP;
END;
/

EXEC load_blobs('YOURDIRECTORY');

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Powered by sweetCaptcha


Trackbacks:0

Listed below are links to weblogs that reference
Loading files into a BLOB column using Oracle External LOB BFILE Data type from De Roeptoeter
TOP
Wordpress Code Snippet by Allan Collins