Example of sqlldr control file

This example is for my own use, as I keep forgetting how to do the followings
- call a function using EXPRESSION
- combine fields
- ignore fields
- do date functions
- vary bindsize/readsize using OPTIONS
- constant varchar
- constant date
- use BOUNDFILLER

So here is one example

options (rows=5000,bindsize=10000000,readsize=10000000)
LOAD DATA
INFILE ‘timepoint.csv’
BADFILE ‘hilda.bad’
APPEND INTO TABLE HD_STG
FIELDS TERMINATED BY “|” ENCLOSED BY ‘”‘
(
ACTIVITY,
SIMULATED_BY,
COUNTERPARTY_ACRONYM,
PRODUCT_GROUP_NAME,
RISK_DATE “to_date(:risk_date,’YYYYMMDD’)”,
CURRENCY,
MTM,
FFR,
MISMATCH_RISK,
EXPOSURE,
filler_simulation_date BOUNDFILLER,
filler_simulation_time BOUNDFILLER,
filler_profile_sequence BOUNDFILLER,
ENTITY,
BATCH_REF,
filler_extract_date BOUNDFILLER,
filler_extract_time BOUNDFILLER,
PROFILE_ID,
BUSINESS_DATE EXPRESSION “TO_DATE(’20101122′,’YYYYMMDD’)”,
SOURCE_SYSTEM CONSTANT ‘ABC’,
SIMULATION_TIMESTAMP EXPRESSION “STA_UTIL_PKG.TO_TIMESTAMP_IN_GMT(:filler_si
mulation_date || :filler_simulation_time, ‘YYYYMMDDHH24:MI:SS’)”,
LAST_UPDATE_TIMESTAMP EXPRESSION “STA_UTIL_PKG.GET_SYSTIMESTAMP_IN_GMT”,
STATUS CONSTANT ‘LOADED’,
SITE_ID CONSTANT ‘LONDON’
)

No comments yet.

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>