Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Unanswered: ORA-01843: not a valid month (need solution on CTL file)

    Hi

    I have an interface managed by a CTL file. The interface is between the data source and the Datawarehouse.

    Some days ago a date field of the source table is changed from mm-dd-yyyy to the new format yyyy-mm-dd

    in the CTL file I have this field:

    ...
    last_update_date,
    ...

    The problem is that when the database returns: ORA-01843: not a valid month

    The solution that I have to implement is manage the data conversion in the CTL file in order to provide to the datawarehouse the date in the correct format (mm-dd-yyyy). But I don't know what I have to add in my CTL File.


    can you help me?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You should apply the correct date format mask.

    Here's an example. I have created a TEST table with two columns:
    Code:
    SQL> desc test
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                                 NUMBER
     DATUM                                              DATE
    This is a control file; note the TO_DATE function:
    Code:
    load data
    infile *
    replace
    into table test
    fields terminated by ','
    trailing nullcols
      (id,
       datum "to_date(:datum, 'yyyy-mm-dd')"
      )
    
    begindata
    1,2010-03-25
    2,2010-04-26
    3,1998-07-13
    Loading session and the result:
    Code:
    SQL> $sqlldr scott/tiger@ora10 control=test9.ctl log=test9.log
    
    SQL*Loader: Release 10.2.0.1.0 - Production on ╚et Srp 1 11:08:00 2010
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 2
    Commit point reached - logical record count 3
    
    SQL> select * from test;
    
            ID DATUM
    ---------- ----------
             1 25.03.2010
             2 26.04.2010
             3 13.07.1998
    
    SQL>

  3. #3
    Join Date
    Jul 2010
    Posts
    3
    The correct data format that I need is mm-dd-yyyy (not the new yyyy-mm-dd)

    can i write this?
    [...]
    last_update_date "to_date(:datum, 'yyyy-mm-dd')",
    [...]

    or I have to change the line?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As far as I understood, recently the date format has changed and now it is YYYY-MM-DD. This is the format mask you need to apply in the control file.

    It would be easier to discuss if you provided a test case. It includes description of a target table (what is, for example, LAST_UPDATE_DATE column's datatype. I *hope* it is a DATE, and not a VARCHAR2 column). What is the input (that would be several lines from your CSV file, just as I wrote in the BEGINDATA section of my control file). Include any relevant information.

  5. #5
    Join Date
    Jul 2010
    Posts
    3
    Yes we have a problem...

    the field in the destination table is VARCHAR2(30 BYTE)

    so I'm trying this in the CTL file


    last_update_date "to_char(trunc(to_date(LAST_UPDATE_DATE,'yyyy-mm-dd hh24:mi:ss')),'mm-dd-yyyy')",


    is the sintax correct?

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you got the correct result, it is correct. Otherwise, it probably isn't.

Tags for this Thread

Posting Permissions

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