Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

date format in sqlldr

user520824Apr 3 2009 — edited Apr 6 2009
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
)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 4 2009
Added on Apr 3 2009
11 comments
6,384 views