I need a help to loading the SQL Loader. A date format has to be done in the control script.
in OPT_EXPRN_D column The below logic has to be included in the ctl file.
Eg: select trunc(to_date('01-JAN-09'),'mm') + (6 - to_char(trunc(to_date('01-JAN-09'),'mm'),'d')) + 14 from dual -- apply kind of this logic in ctl file
OPTIONS(ERRORS=100000, ROWS=2000, BINDSIZE=300000)
LOAD DATA
INFILE '/home/btchproc/load_process/ftp_home/ACATTRAN.DAT'
BADFILE '/home/btchproc/load_process/bad_files/acattran.bad'
INTO TABLE acat_fact_test
APPEND
(
ACE_FACT_I POSITION(01:10) INTEGER EXTERNAL,
PRO_DIMN_I POSITION(11:20) INTEGER EXTERNAL,
ACAT_PRCS_D POSITION(45:54) DATE "YYYY-MM-DD" nullif acat_prcs_d=BLANKS,
BRN_I POSITION(55:57) CHAR "NVL(:brn_i,' ')",
FA_I POSITION(58:63) CHAR "NVL(:FA_I,' ')",
OPT_EXPRN_D POSITION(904:913) DATE "YYYY-MM-DD" nullif trunc(:opt_exprn_d,'mm') + (6 - to_char(trunc(:opt_exprn_d,'mm'),'d')) + 14 =BLANKS
)