Load files into BLOB column

You can use SQL Loader (sqlldr) to load blobs. There is a LOBFILE() field type for sql loader control files that can be used to read the BLOB column from a filename for each row in the input file.

Something like:

So now your main task is just to generate the list of filenames into a sql loader control file as below.

I would write a script to do so. Do the IDs correlate with the file naming convention, or do you expect to generate the IDs from a sequence?

sqlldr scott/tiger control=tifflob.ctl

-- tifflob.ctl
LOAD DATA 
INFILE *
APPEND INTO TABLE load_blob
FIELDS TERMINATED BY ','
(
 id            INTEGER EXTERNAL,
 tif_filename  FILLER, -- skip, but use for reference below
 tif_file      LOBFILE(tif_filename) TERMINATED BY EOF
)
BEGINDATA
001,C:\load_file\file001.tif
002,C:\load_file\file002.tif
003,C:\load_file\file003.tif

Select allOpen in new window

mrjoltcola,
in my table LOAD_BLOB I have already ID values.
In your script I must write 2000 times the file name

As I said above, do the IDs correlate with the file naming convention? If so, how.

You must have a way to correlate the row to the file. Tell me how and I'll help you with a simple script to generate the 2000 filenames.

For ID 001, is the filename 001.tif ?

Can I load only .tif file?

LOAD DATA
INFILE *
APPEND INTO TABLE load_blob
FIELDS TERMINATED BY ','
(
 tif_filename  FILLER, -- skip, but use for reference below
 tif_file      LOBFILE(tif_filename) TERMINATED BY EOF
)
BEGINDATA
C:\load_file\file001.tif
C:\load_file\file002.tif
C:\load_file\file003.tif

I tried this script:

CREATE DIRECTORY load_file AS 'c:\load_file';

GRANT READ ON DIRECTORY load_file TO COMTS;

DECLARE
      bfile_pointer BFILE;
      blob_pointer BLOB;
      bfile_offset NUMBER :=1;
      blob_offset NUMBER :=1;
      tot_len INTEGER;
   
BEGIN
   
    INSERT INTO load_blob VALUES (1,EMPTY_BLOB());
 
     SELECT blob_data INTO blob_pointer FROM load_blobWHERE id = 1 FOR UPDATE;
 
     bfile_pointer :=bfilename('image','00000001_00000001.tif');
 
     dbms_lob.fileopen(bfile_pointer,dbms_lob.file_readonly);
 
     dbms_lob.OPEN(blob_pointer,dbms_lob.lob_readwrite);
 
     dbms_lob.LOADBLOBFROMFILE(blob_pointer,bfile_pointer,dbms_lob.lobmaxsize,bfile_offset,blob_offset);
 
     tot_len :=DBMS_LOB.GETLENGTH(blob_pointer);
 
     dbms_lob.close(blob_pointer);
 
     dbms_lob.fileclose(bfile_pointer);
 
     DBMS_OUTPUT.PUT_LINE(TO_CHAR(tot_len));
END;
   /

this procedure load correcly the file '00000001_00000001.tif'

Can I use this procedure to load all 2000 files???