This example shows how to insert records from a source file with fixed-width columnar data.
Destination table
Create the table that is to be filled:
create table tq84_sql_loader_1 (
load_time date,
num number(7,2),
txt_1 varchar2(7),
txt_2 varchar2(10)
);
Control file
Note that the positions 11 through 20 are loaded into field_1 and positions 1 through 10 into field_2 (the column and input data order is different).
The field load_time is filled with the current time (sysdate) of the load.
options (
errors = 0
)
load data
infile
'load_1.dat'
insert
-- preserve blanks
--
-- Note, uncommenting 'preserve blanks' will
-- preserve blanks on an input string's right side, but
-- not on an input string's left side.
-- Null/empty numbers won't be loaded with
-- preserve blanks, though!
--
into table tq84_sql_loader_1
(
load_time sysdate,
num position( 1: 8),
txt_1 position(10:16), -- Note the gap (column 9)
txt_2 position(18:27)
)