SQL*Loader (SQLLDR):
Sample-1: Load a single column field into table.
-- Load a single field into file - records delimited by line feed.
-- INSERT data
-- load_single_field.ctl
options (rows=1000,errors=9999)
load data
infile my_single_field_file.dat
badfile bad_records_file.bad
insert
into table myschema.single_field
fields terminated by '' trailing nullcols
(
field_1
)
Sample-2: A simple SQLLDR control file
-- Load comma delimited fields (state id, state short code and state name) into state table. Truncate and replace data.
-- load_state_codes.ctl
options (rows=100)
load data
infile state_details.dat
badfile state_bad_recs.bad
truncate
into table state_info
fields terminated by "," trailing nullcols
(
state_id,
state_abbr,
state_desc
)
Sample-3: SQLLDR control file with direct path load
-- Load comma delimited fields (state id, state short code and state name) into state table. Truncate and replace data, specify file characterset when different than DB characterset.
-- load_state_codes.ctl
options (rows=100,errors=999, direct=true)
unrecoverable
load data CHARACTERSET WE8ISO8859P1
infile state_details.dat
badfile state_bad_recs.bad
truncate
into table state_info
fields terminated by "," trailing nullcols
(
state_id,
state_abbr,
state_desc
)
-- Load comma delimited fields ( id, name, birth date (DDMMYYYY format), preferred name (skip this field), postal code. Default the lanugage code to "ENG".
-- load_cust_data.ctl
options (rows=100,errors=999, direct=true)
unrecoverable
load data
infile customers.dat
badfile cust_bad_recs.bad
truncate
into table customers
fields terminated by "," trailing nullcols
(
cust_id,,
cust_name,
birth_date date 'DDMMYYYY',
skip_pref_name filler, -- skipped
postal_cd,
language_cd constant 'ENG'
)
Sample-5: SQLLDR control file with recnum, function calls, nullifs etc.
-- Comma delimited fields, sample to illustrate many available options.
-- load_cust_data.ctl
options (rows=100,errors=999, direct=true)
unrecoverable
load data
infile customers.dat
badfile cust_bad_recs.bad
truncate
into table customers
fields terminated by "," trailing nullcols
(
cust_id,
cust_name NULLIF cust_name="NULL",
rec_seq_num recnum, -- row number from file - generated value
birth_date date 'DDMMYYYY',
apply_dt date 'DDMMYYYY' NULLIF apply_dt="00000000",
skip_pref_name filler, -- skipped
postal_cd,
country_code "decode(:country_code, NULL, 'USA', :country_code)",
my_short_pre "substr(:my_short_pre,1,3)",
my_gen_value "myschema.utilpkg.get_secret_code(:my_gen_value)", -- user defined
language_cd constant 'ENG',
update_tmstp sysdate
)
Sample-6: Load a single file into multiple tables based on record type in first field
-- load_sales_orders.ctl
-- First field is H (Header), D (Detail) or S (Shipments)
-- Fields terminated by "|"
options (rows=100,errors=9999)
load data CHARACTERSET UTF8
infile sales_orders.dat
badfile bad_sales_orders.bad
truncate
into table order_details_stag when recordid='D'
fields terminated by "|" trailing nullcols
(
recordid filler position(1) char,
sales_order_id,
order_detail_line_id,
product_id,
quantity,
...
...
)
into table order_header_stag when recordid='H'
fields terminated by "|" trailing nullcols
(
recordid filler position(1) char,
sales_order_id,
order_dt DATE 'DDMMYYYY',
buyer_id,
total_amt,
...
...
)
into table order_shipments_stag when recordid='S'
fields terminated by "|" trailing nullcols
(
recordid filler position(1) char,
sales_order_id,
ship_line_itm,
shipper_name,
...
...
)