If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Importing data through sqlldr

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On