Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    3

    Unanswered: How to handle wrong dates in sql loader.

    I am working on Sql*loader.In my csv file or flat file,i have date records and few records having date like 31-feb-2010.now am loading data into my table, at that time am getting error due to that wrong date(31-feb-2010).because of this no record was inserted into my table.
    here what i want is
    1.if am getting this error also ,i want to get inserted all remaining records into my table except that error record.
    2.if am getting wrong dates in Csv file .how to handle that error while writing control file.
    Please give solution for this.

    Thanks in advance.
    Pavani

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by pavani.vnc View Post
    1.if am getting this error also ,i want to get inserted all remaining records into my table except that error record.
    In controlfile, first line: OPTIONS (ERRORS=0)
    2.if am getting wrong dates in Csv file .how to handle that error while writing control file.
    Please give solution for this.
    Once all records are loaded, fix the data in the "bad" file and load that file.
    -- Or --
    Create external table (bypass sql*loader) and validate + load the data using sql.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Or, write a function that'll accept "date" values from the CSV file, decide whether it is valid or not (and fix it if that's the case) and return a valid value. You'd use that function in SQL*Loader control file, such as
    Code:
    ...
    date_column date "my_date_fix_function (:date_column)",
    ...

  4. #4
    Join Date
    Jan 2012
    Posts
    3

    hi

    Once all records are loaded, fix the data in the "bad" file and load that file.
    ---thank you so much.
    now its working now.
    -- Or --
    Create external table (bypass sql*loader) and validate + load the data using
    sql.
    --can explain in detail because how to bypass sql*loader.

    Thanks in advance.
    Pavani.

  5. #5
    Join Date
    Jan 2012
    Posts
    3
    write a function that'll accept "date" values from the CSV file, decide whether it is valid or not (and fix it if that's the case) and return a valid value. You'd use that function in SQL*Loader control file,
    such as ...date_column date "my_date_fix_function (:date_column)",
    --------
    Okay but how function will take date values from CSV file.

    thanks
    Pavani.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by pavani.vnc View Post
    --can explain in detail because how to bypass sql*loader.
    .
    When you create external table, you can query the csv data as regular table using sql commands.

    Check out the "Organization External" clause in the fine Oracle® Database SQL Language Reference.
    Last edited by LKBrwn_DBA; 01-06-12 at 11:07.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by pavani.vnc View Post
    Okay but how function will take date values from CSV file.
    As I told you in my previous message; didn't you see that piece of code?

Posting Permissions

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