Use SQL*Loader to load LOB

1. Create a table with a BLOB column:
   

   CREATE TABLE images (
      id NUMBER,
      image BLOB
   );
   

2. Use PL/SQL to load the image:
   You can write a PL/SQL block to load an image from the filesystem into the BLOB column. Here’s a simple example using Oracle's `DBMS_LOB` package to load an image file from the server's filesystem:
   

   DECLARE
      l_blob BLOB;
      l_bfile BFILE := BFILENAME('DIRECTORY_NAME', 'filename.jpg');
   BEGIN
      INSERT INTO images (id, image) VALUES (1, EMPTY_BLOB())
      RETURNING image INTO l_blob;
   
      DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
      DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));
      DBMS_LOB.fileclose(l_bfile);
   
      COMMIT;
   END;
   

   
   In this code:
   • DIRECTORY_NAME should be an Oracle directory object pointing to the directory where your files are stored.
   • filename.jpg is the name of the image file you want to load.
   • EMPTY_BLOB() initializes the BLOB.
3. Accessing and manipulating the BLOB data: Once stored in the database, you can access and manipulate the BLOB data using SQL and PL/SQL for various purposes like retrieval, update, and backup.

LOAD DATA
INFILE <dataFile>
APPEND INTO TABLE <tableName>
FIELDS TERMINATED BY '<separator>'
(<list of all attribute names to load>)

1. < dataFile> is the name of the data file. If you did not give a file name extension for <dataFile>, Oracle will assume the default extension ".dat". Therefore, it is a good idea to name every data file with an extension, and specify the complete file name with the extension.
2. <tableName> is the name of the table to which data will be loaded. Of course, it should have been created already before the bulk load operation.
3. The optional keyword APPEND says that data will be appended to <tableName>. If APPEND is omitted, the table must be empty before the bulk load operation or else an error will occur.
4. <separator> specifies the field separator for your data file. This can be any string. It is a good idea to use a string that you know will never appear in the data, so the separator will not be confused with data fields.
5. Finally, list the names of attributes of <tableName> that are set by your data file, separated by commas and enclosed in parentheses. This list need not be the complete list of attributes in the actual schema of the table, nor must it be arranged in the same order as the attributes when the table was created -- sqlldr will match attributes to by their names in the table schema. Any attributes unspecified in the list of attributes will be set to NULL.

sqlldr userid=rjb/rjb control=part1.ctl direct=true parallel=true
sqlldr userid=rjb/rjb control=part2.ctl direct=true parallel=true
sqlldr userid=rjb/rjb control=part3.ctl direct=true parallel=true

• Fixed Record Format
  A file is in fixed record format when all records in a datafile are the same byte length. Although this format is the least flexible, it results in better performance than variable or stream format. Fixed format is also simple to specify. For example:
  

  INFILE datafile_name "fix n"
  

  
  This example specifies that SQL*Loader should interpret the particular datafile as being in fixed record format where every record is n bytes long.
  Example 5-3 shows a control file that specifies a datafile that should be interpreted in the fixed record format. The datafile in the example contains five physical records. Assuming that a period (.) indicates a space, the first physical record is [001,...cd,.] which is exactly eleven bytes (assuming a single-byte character set). The second record is [0002,fghi,\n] followed by the newline character (which is the eleventh byte), and so on. Note that newline characters are not required with the fixed record format. Note that the length is always interpreted in bytes, even if character-length semantics are in effect for the file. This is necessary because the file could contain a mix of fields, some of which are processed with character-length semantics and others which are processed with byte-length semantics.

 
load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1, col2)
example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,