Loading files as CLOB or BLOB column using sqlldr

This article shows how to load text or binary files into Oracle database table column using sqlldr tool. It’s very useful and common practices.

Let’s prepare some dummy files to load. I have just created four text files:

  • file1
  • file2
  • file3
  • file4


$ echo "file number 1" > file1
$ echo "file number 2" > file2
$ echo "file number 3" > file3
$ echo "file number 4" > file4


C:\Users\tomasz>echo file number 1 > file1
C:\Users\tomasz>echo file number 2 > file2
C:\Users\tomasz>echo file number 3 > file3
C:\Users\tomasz>echo file number 4 > file4

1. Prepare table that will store your files as CLOB or BLOB

create table load_files
  file_name varchar2(256),
  file_type varchar2(10) 
  constraint c_file_type check(file_type in ('TEXT', 'BINARY')),
  file_text clob,
  file_binary blob

2. Prepare text file “files_to_load.txt” with list of files to load. First column defines file name to load. Second column defines type of file to load:

  • TEXT – will load file into CLOB column
  • BINARY – will load file into BLOB column

3. Prepare sqlldr control file “load_files.ctl”

load data 
infile 'files_to_load.txt'
into table load_files
fields terminated by ','
  file_name   char(256),
  file_type   char(10),
  file_text   lobfile(file_name) terminated by eof 
                                 nullif file_type='BINARY',
  file_binary lobfile(file_name) terminated by eof 
                                 nullif file_type='TEXT'

4. Run sqlldr

sqlldr userid=tomasz/tomasz@ora12C control=load_files.ctl log=load_files.log bad=load_files.bad;

5. Review data in table

  file_name, file_type, 
  dbms_lob.getlength(file_text) file_size_text, 
  dbms_lob.getlength(file_binary) file_size_binary
from load_files;

--------- --------- -------------- ----------------
file1     TEXT      14    
file2     TEXT      14    
file3     BINARY                   14
file4     BINARY                   14

Have  a fun 🙂



Leave a Reply

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