Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    Austin, TX
    Posts
    11

    Question Importing data through sqlldr

    I'm trying to import some data into Oracle through sqlldr and having real problems.

    I create the table I will be inserting the data into:
    CREATE TABLE IPs (
    host VARCHAR2 (15) NOT NULL,
    port NUMBER (4) NOT NULL,
    secs NUMBER (6) NOT NULL,
    bin NUMBER (1) NOT NULL,
    unixdate NUMBER (11) NOT NULL,
    unixdate2 NUMBER (11) NOT NULL)

    My control file (IPs.ctl) looks like this:
    LOAD DATA
    INFILE 'IP.txt'
    REPLACE INTO TABLE IPs

    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'"
    (
    host,
    port,
    secs,
    bin,
    unixdate,
    unixdate2
    )

    Lastly I run the sqlldr script:
    sqlldr user/password control=IPs.ctl direct=true

    When I apply this to data of the form:
    207.104.73.10,8000,617,1,1051284866,1067626861
    207.104.73.11,80,242,1,1042979187,1067618706
    207.104.73.12,3128,227,0,1051549235,1067626264
    207.104.73.13,80,343,1,1062322212,1067608319
    207.104.73.14,80,603,1,1063935185,1067626019
    207.104.73.15,8000,4142,1,1064364883,1067610755

    I get the following error for all rows "Record X: Rejected - Error on table IPs, column unixdate2 ORA-01722: invalid number" --- meaning that no data is imported. I do not believe the problem is with the unixdate2 field since eliminating that field from the process just causes the error to migrate to the previous listed field (unixdate).

    I can't figure out the cause of this error and get my data loaded.

    ------
    I case this information helps to debug this problem, when I add an extra, unwanted field (DATE1) to the end of my data everything works fine -- but creating that extra field is quite a bit of work, so I really need to be able to run the query without it. Here's how things look with the field added:

    CREATE TABLE IPs (
    host VARCHAR2 (15) NOT NULL,
    port NUMBER (4) NOT NULL,
    secs NUMBER (6) NOT NULL,
    bin NUMBER (1) NOT NULL,
    unixdate NUMBER (11) NOT NULL,
    unixdate2 NUMBER (11) NOT NULL,
    DATE1 DATE NOT NULL
    and

    LOAD DATA
    INFILE 'IP.txt'
    REPLACE INTO TABLE IPs

    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'"
    (
    host,
    port,
    secs,
    bin,
    unixdate,
    unixdate2,
    Date1 DATE 'mm/dd/yyyy'
    )

    and apply this to
    207.104.73.10,8000,617,1,1051284866,1067626861,10/22/2003
    207.104.73.11,80,242,1,1042979187,1067618706,10/22/2003
    207.104.73.12,3128,227,0,1051549235,1067626264,10/22/2003
    207.104.73.13,80,343,1,1062322212,1067608319,10/22/2003
    207.104.73.14,80,603,1,1063935185,1067626019,10/22/2003
    207.104.73.15,8000,4142,1,1064364883,1067610755,10/22/2003

    I really appreciate any help you can provide. Thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    i guess it's the first value in every row, the host. That's a varchar2 so it should be enclosed by ' ' (single quotes). Normally oracle can do implicit conversion of numbers to characters, but oracle doesn't understand the format of 123.123.123.123. That is not a valid number, so it cannot be converted to a character.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Sep 2003
    Location
    Austin, TX
    Posts
    11
    Thank you Edwin for your help.

    After reading your response I tried adding single-quotes to the input data as seen below:
    '207.104.73.10',8000,617,1,1051284866,1067626861
    '207.104.73.11',80,242,1,1042979187,1067618706
    '207.104.73.12',3128,227,0,1051549235,1067626264
    '207.104.73.13',80,343,1,1062322212,1067608319
    '207.104.73.14',80,603,1,1063935185,1067626019
    '207.104.73.15',8000,4142,1,1064364883,1067610755

    However, I still get the same error message.

    To corroborate this, when I reduce the inputs to just import the "host" field (without single-quotes) -- in other words, only importing one field, things run fine.

    So I'm still stumped.

    Thanks again.

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    the problem might then be the last column, which is a date. Maybe u can try to use sqlldr without the last column to check that's the problem?
    For now i don't know the exact format for this, it let's just figure out if that column is the problem

    greetz
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Maybe some strange invisible character at the end of the line ?

    Try enclosing the last column in " " eg

    207.104.73.10,8000,617,1,1051284866,"1067626861"

    or add an extra comma

    207.104.73.10,8000,617,1,1051284866,1067626861,

    Beware: i'm not a sqlloader expert ... ;-)

    HTH
    Alberto

  6. #6
    Join Date
    Sep 2003
    Location
    Austin, TX
    Posts
    11

    Smile Thanks

    Thank you Edwin and Alberto for your help. After reading your replies I did try enclosing the last column in quotes. Everything ran fine when I did this.

    For my process, I needed to be able to run things without changing the data (so I would not be able to add quotes to the last column). However, the comments you made led me to an option in SQL Loader.
    By adding "TERMINATED BY WHITESPACE" right after the last column is listed in the control file, SQL Loader was able to recognize the end of that field. (Apparently there were some hidden whitespace characters at the end of the line.)

    So my control file now looks like this (and runs fine):
    LOAD DATA
    INFILE 'IP.txt'
    REPLACE INTO TABLE IPs

    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'"
    (
    host,
    port,
    secs,
    bin,
    unixdate,
    unixdate2 TERMINATED BY WHITESPACE
    )

    Thanks again for all your help!

Posting Permissions

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