Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Angry Unanswered: Invalid date format while SQLLDR

    Hi ALL
    I am using Sqlldr to load the data......
    While loading i am getting the error that " not a valid month" on one of the column

    On checking my .csv file having one of the column with date entries...Format is MM/DD/YYYY
    But my database is having date format DD-MM-YYYY

    I have tried to change the format of my database using nls_date_format
    using alter system/session set nls_date_format='MM/DD/YYYY'

    this changes the value for nls_date_format but when i query database_properties its givivng me the old format.

    but still the problem is same.

    How can achive this.....
    either by changing the date format of the database (How to change the date format of the database) OR
    What kind of change to i need to make while loading the thru sqlldr...

    Here are the dertails of my control file , table and .csv file

    CREATE TABLE T_MEAS_WI01_TEMP
    (
    ACNA varchar2(3),
    YEARMO varchar2(6),
    retail number(1),
    STATE varchar2(2),
    METRO varchar2(25),
    DPID number(5),
    NUM number(8),
    DEN number(8),
    so_nbr varchar2(14),
    main_tn number(10),
    ddcomp date,
    storage_dt date,
    ......

    LOAD DATA
    INFILE 'C:\csvfiles\WI01\re001D_wi_sta_une.csv'
    BADFILE 'C:\csvfiles\WI01\re001D_wi_sta_une.bad'
    DISCARDFILE 'C:\csvfiles\WI01\re001D_wi_sta_une.dsc'
    insert
    INTO TABLE t_meas_wi01_tmp
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    ACNA,
    YEARMO,
    retail,
    STATE,
    METRO,
    DPID,
    NUM,
    DEN,
    so_nbr,
    main_tn,
    ddcomp,
    storage_dt,
    pon_rpon,
    dsl,
    fvi,
    stc,
    busid,
    seg_cd,
    cls_name,
    cmt_msd,
    not_met,
    unep,
    adsr,
    prod_nm,
    rptd_nm,
    cktid,
    ..........




    Record 2: Rejected - Error on table T_MEAS_WI01_TMP, column DDCOMP.
    ORA-01843: not a valid month



    plz suggest me how can i ressolve the issue.......

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    in your control file use

    main_tn,
    ddcomp "MM/DD/YYYY",
    storage_dt,
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jul 2009
    Posts
    7
    Thanks you for your reply
    but Now its giving me other error for next date OR last date entry in control file .stating

    ORA-00984: column not allowed here

    i.e. over the column which is having the last date entry in control file

  4. #4
    Join Date
    Jul 2009
    Posts
    7
    i.e. if you can see next column is storage_dt ,this also is having date format .So i use same over there also bt this time its giving me above error ORA-00984: column not allowed here
    over storage_dt column

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    put in a line of the actual data into this thread
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Feb 2005
    Posts
    57
    according to the manual format is actually:

    ddcomp DATE "MM/DD/YYYY",

    Field List Reference

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •