Problem with sql loader control file date format

in Oracle Development and Fusion Middleware; I have table with a column defined like INSTATE_DATE DATE I have to laod the data frm the csv file ...
+ Post Reply + Post New Topic
Results 1 to 10 of 10
  1. #1
    anijan is offline Junior Member
    Points: 150, Level: 2
    Join Date
    03 Mar 2010
    Posts
    5
    Points
    150

    Unhappy Problem with sql loader control file date format

    I have table with a column defined like
    INSTATE_DATE DATE
    I have to laod the data frm the csv file to this table. my csv file has data 00/00/0000. Even though it is an invalid date
    I have load this date to my oracle table. I have an option of loading a default value say 01/01/2010 everytime i come across 00/00/0000.
    I tried to modify the control file like
    INSTATE_DATE DATE "replace(:INSTATE_DATE,'00/00/0000','01/01/2010')"
    Even then im getting the error "ORA-01821: date format not recognized"
    Please let me know if there are any other way to achive this.


  2. #2
    Sadik's Avatar
    Sadik is offline Administrator
    Points: 35,390, Level: 100
    Join Date
    03 Aug 2008
    Location
    Kolkata, India
    Posts
    1,860
    Points
    35,390

    Re: Problem with sql loader control file date format

    I am curious, why can't you just do a find and replace in excel?
    Learn Oracle with Oracle forum. Check out General Discussion Forums

  3. #3
    zargon's Avatar
    zargon is offline Forum Guru
    Points: 26,260, Level: 96
    Join Date
    22 Oct 2008
    Location
    Aurora, CO
    Posts
    2,015
    Points
    26,260

    Re: Problem with sql loader control file date format

    Quote Originally Posted by anijan View Post
    I have table with a column defined like
    INSTATE_DATE DATE
    I have to laod the data frm the csv file to this table. my csv file has data 00/00/0000. Even though it is an invalid date
    I have load this date to my oracle table. I have an option of loading a default value say 01/01/2010 everytime i come across 00/00/0000.
    I tried to modify the control file like
    INSTATE_DATE DATE "replace(:INSTATE_DATE,'00/00/0000','01/01/2010')"
    Even then im getting the error "ORA-01821: date format not recognized"
    Please let me know if there are any other way to achive this.

    Do this in the .ctl file:

    ..
    into table ...
    WHEN instate_date = '00/00/0000'
    (...
    instate_date date "to_date('01/01/4000','MM/DD/RRRR')",
    ...)
    into table ...
    when instate_date <> '00/00/0000'
    (...
    instate_date date "to_date(:instate_date,'MM/DD/RRRR')",
    ...)
    Last edited by zargon; 03-05-2010 at 03:26 PM.

  4. #4
    anijan is offline Junior Member
    Points: 150, Level: 2
    Join Date
    03 Mar 2010
    Posts
    5
    Points
    150

    Re: Problem with sql loader control file date format

    Still im getting the same error
    ORA-01821: date format not recognized

    is it becos of the invalid date 00/00/0000.?

  5. #5
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: Problem with sql loader control file date format

    Try with boundfiller.. .


    Code sql:
     
    LOAD DATA
    INFILE *
    INTO TABLE date_table
    FIELDS TERMINATED BY ','
    TRAILING NULLCOLS
    (
    seq INTEGER external,
    xyz boundfiller,
    dateq "NVL(Decode(:xyz,'00/00/0000',TO_DATE('01/01/2010','dd/mm/yyyy'),TO_DATE(:xyz,'dd/mm/yyyy')),TO_DATE('01/01/2010','dd/mm/yyyy'))"
    )
    BEGINDATA
    1,05/05/1980
    2,
    3,00/00/0000
    4,01/01/2008


    Code sql:
    SQL> SELECT * FROM date_table;
     
           SEQ DATEQ
    ---------- ---------
             1 05-MAY-80
             2 01-JAN-10
             3 01-JAN-10
             4 01-JAN-08
     
    SQL>

    Raj.

  6. #6
    anijan is offline Junior Member
    Points: 150, Level: 2
    Join Date
    03 Mar 2010
    Posts
    5
    Points
    150

    Re: Problem with sql loader control file date format

    xyz boundfiller,
    dateq "NVL(Decode(:xyz,'00/00/0000',TO_DATE('01/01/2010','dd/mm/yyyy'),TO_DATE(:xyz,'dd/mm/yyyy')),TO_DATE('01/01/2010','dd/mm/yyyy'))"

    can u explain what is dateq.? do i have to give a comma next to bounfiller.?

  7. #7
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: Problem with sql loader control file date format

    Boundfiller is user to skip the column for time being, but they should be used in another field.
    Filler and Boundfiller

    Here Seq and Dateq are the column names in tables. Second field in the file is treated as boudfiller and used against Dateq for building a logic.

    There is comma after Boundfiller.

    Raj.

  8. #8
    anijan is offline Junior Member
    Points: 150, Level: 2
    Join Date
    03 Mar 2010
    Posts
    5
    Points
    150

    Re: Problem with sql loader control file date format

    Thanks for ur quick reply Raj.

    Sorry to trouble you again.

    if I frame my control file like
    LOAD DATA
    APPEND
    INTO TABLE MYTABLE
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    (
    ASSET CHAR "decode(:ASSET,null,' ',:ASSET)",
    INSTATE boundfiller,
    NEW_INSATE CHAR "decode(:NEW_INSATE,null,' ',:NEW_INSATE)",
    )
    then the column INSTATE is skipped while loading.
    but the invalid date 00/00/0000 will not be present in all the records of the csv file. so we cannot skip the entire column.

    As you suggested i used decode like,
    LOAD DATA
    APPEND
    INTO TABLE MYTABLE
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    (
    ASSET CHAR "decode(:ASSET,null,' ',:ASSET)",
    INSTATE DATE "NVL(Decode(:INSTATE_DATE,'00/00/0000',TO_DATE('01/01/9000','dd/mm/yyyy'),TO_DATE(:INSTATE,'dd/mm/yyyy')),TO_DATE('01/01/9000','dd/mm/yyyy'))",
    NEW_INSATE CHAR "decode(:NEW_INSATE,null,' ',:NEW_INSATE)",
    )

    still im getting date format not recognised error.
    Pls correct if im wrong..

  9. #9
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: Problem with sql loader control file date format

    I assume your table has two columns .

    ASSET &
    INSTATE

    And the file have two fields

    ASSET &
    INSTATE

    Then the code should look like.

    Code sql:
    LOAD DATA
    APPEND
    INTO TABLE MYTABLE
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    (
    ASSET CHAR "NVL(:ASSET,' ')",
    NEW_INSATE boundfiller,
    INSTATE DATE "NVL(Decode(:NEW_INSATE,'00/00/0000',TO_DATE('01/01/9000','dd/mm/yyyy'),TO_DATE(NEW_INSATE,'dd/mm/yyyy')),TO_DATE('01/01/9000','dd/mm/yyyy'))"
    )

    Raj.

  10. #10
    anijan is offline Junior Member
    Points: 150, Level: 2
    Join Date
    03 Mar 2010
    Posts
    5
    Points
    150

    Re: Problem with sql loader control file date format

    Thanks a lot Raj..
    It is working absolutely fine.

    I have one more query.

    I have a column ASSET in my CSV file. But the column is not defined in the table. how do i skip this column in CSV file while loading it to the table. should the control file be modified.?

Other Solutions
  1. Error on sql loader log file
    By Aj_aaron2002@yahoo.co.uk in forum SQL PL/SQL
    Replies: 3
    Last Post: 08-24-2010, 11:48 PM
  2. control file help
    By dsub42 in forum Oracle Database
    Replies: 10
    Last Post: 04-14-2010, 05:27 PM
  3. Replies: 1
    Last Post: 11-22-2009, 08:56 PM
  4. Problem in date comparison
    By JLone in forum SQL PL/SQL
    Replies: 2
    Last Post: 09-25-2009, 12:39 PM
  5. Replies: 3
    Last Post: 08-27-2009, 02:30 PM