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
Unix
$ echo "file number 1" > file1 $ echo "file number 2" > file2 $ echo "file number 3" > file3 $ echo "file number 4" > file4
Windows
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
file1,TEXT file2,TEXT file3,BINARY file4,BINARY
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
select file_name, file_type, dbms_lob.getlength(file_text) file_size_text, dbms_lob.getlength(file_binary) file_size_binary from load_files; FILE_NAME FILE_TYPE FILE_SIZE_TEXT FILE_SIZE_BINARY --------- --------- -------------- ---------------- file1 TEXT 14 file2 TEXT 14 file3 BINARY 14 file4 BINARY 14
Have a fun 🙂
Tomasz