|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
Example 2 for SQL*Loader
This example shows how to insert records that are seperated by a comma.
First, the table to be filled is created:
create table sql_loader_2 ( field_1 varchar2(10), field_2 number );
Here's the control file. Note, that the second field is specified as integer external. That means, that the numbers in the dat file are human readable rather than "real" bytes.
load data infile 'load_2.dat' "str '\r\n'" insert into table sql_loader_2 fields terminated by ',' ( field_1 char, field_2 integer external )
Here's the data. The name of the file (load_2.dat) had been specified with the infile statement in the control file.
one,1 two,2 fifty,50 eighty-eight,88 one hundred,100 fifteen,15
The following command actually loads the data.
sqlldr control=load_2.ctl userid=rene/rene
The table now contains (
FIELD_1 FIELD_2 ---------- ---------- one 1 two 2 fifty 50 fifteen 15
Two records could not be loaded, they appear in the bad file:
eighty-eight,88 one hundred,100