Loading a Flat File to an Oracle Table
When loading a flat file into Oracle, the fields in the flat file
should be separated by delimiters such as tabs or commas. The
delimiter can be specified in the control file. The loader will map
the fields in the file to the columns in the table. The first field
is stored in the first column; the second field is stored in the
second column, etc. If there are more fields than columns in the
table, the remaining fields will be ignored. If there are less fields
than columns in the table, the missing fields will be inserted as
null values in the associated columns.
This example loads a flat file called infile.dat to an
Oracle table named oracle_2_table with two columns of type
INTEGER and VARCHAR(20). To load, run the sqlldr
command from the shell as follows:
The contents of the control file ctrl_file.txt are:
To specify a different terminator, such as a comma,
change the fourth line above to:
shell> sqlldr userid=userid@sid, control=ctrl_file.txt, log=log, errors=100
123\tString1
234\tString2
load data
infile 'infile'
append into table oracle_2_table
fields terminated by ' '
trailing nullcols
(col1 decimal(13,0),
col2 varchar(40))
fields terminated by ','
If oracle_2_table is already defined, you can use the following syntax for
the control file ctrl_file.txt:
load data
infile 'infile'
append into table oracle_2_table
fields terminated by ' '
trailing nullcols
(col1,
col2)
Post a comment