Home » RDBMS Server » Server Utilities » SQL loader problem
SQL loader problem [message #292795] Wed, 09 January 2008 21:10 Go to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
I am loading data from csv to oracle.
I have attached the control file.

This file rejects
en017048,PD,N7C001013,cs,axp,usd,12202007,L,25.00,51.03,51.03,54.81,54.81,54.81,54.81,1370.14,1370.14,1370.14,1370.14,18.00,18.00,127 5.75,1275.75,-94.39,-94.39,-94.39,-94.39,0.00,0.00,usd,-94.39,-94.39,0.00,0.00,-94.39,-94.39,0.00,0.00

But reads
en017048,PD,N7C001054,ca,pgrxx,usd,12202007,L,,,,,,,,60.93,60.93,60.93,60.93,0.00,0.00,60.93,60.93,,,,,0.00,0.00,usd,,,,,,,,

when it rejects it says error that Error at column ltrepGL(last field) Invaild number.

Please advice.

[Updated on: Wed, 09 January 2008 21:17]

Report message to a moderator

Re: SQL loader problem [message #292828 is a reply to message #292795] Thu, 10 January 2008 00:12 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Couple of doubts...

Is the one, which is erring out, the last row in data file?
Does it erring out for any other row?
Any other clue to differ that row, which is erring out, from other rows?

By
Vamsi
Re: SQL loader problem [message #292832 is a reply to message #292828] Thu, 10 January 2008 00:21 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
For the first doubt...no. I gave a sample only.
many records are rejected. similar to that...(all are having 0.00 at the last field)

I gave conatant 0.00 in control file...
Then it accepts all the rows...
I am new to this sqlloader. I am contnuing my friend's work.

As per my knowledge, if the sql loader finds number at the last field, it rejects.
Re: SQL loader problem [message #292859 is a reply to message #292795] Thu, 10 January 2008 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Is there sufficient fields in your line?

Regards
Michel
Re: SQL loader problem [message #292863 is a reply to message #292859] Thu, 10 January 2008 01:30 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Yes. I am getting.

Please read the attachment.
Re: SQL loader problem [message #292868 is a reply to message #292863] Thu, 10 January 2008 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I can't download xls file.

Regards
Michel
Re: SQL loader problem [message #292871 is a reply to message #292868] Thu, 10 January 2008 01:47 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Ok.
I have all the fields in the table as per the control file.
the sql loader rejects the record if it finds any numeric data at the last.
Otherwise it accepts. Please refer the sample datas.
Re: SQL loader problem [message #292872 is a reply to message #292863] Thu, 10 January 2008 01:52 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Is it 0.00,
or 0.00

By
Vamsi
Re: SQL loader problem [message #292874 is a reply to message #292872] Thu, 10 January 2008 01:54 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
It is 0.00
If no data in the last field, then accepts.
Re: SQL loader problem [message #292877 is a reply to message #292874] Thu, 10 January 2008 02:00 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
I have asked the question because it is "0.00," in the excel file, which you have uploaded.

By
Vamsi
Re: SQL loader problem [message #292881 is a reply to message #292877] Thu, 10 January 2008 02:04 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
please read the note below that

"the bolded is just 0.00 . But while puuting here it becomes 0.
so I used comma for that."

while pasting it here, without comma...it will become zero.
To differentiate that, I have put comma.
Please note.
Re: SQL loader problem [message #292887 is a reply to message #292881] Thu, 10 January 2008 02:12 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Oops! I didn't notice that.

By
Vamsi
Re: SQL loader problem [message #292986 is a reply to message #292795] Thu, 10 January 2008 07:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:

As per my knowledge, if the sql loader finds number at the last field, it rejects.


No . Never , unless there is Error .

Thumbs Up
Rajuvan
Re: SQL loader problem [message #292987 is a reply to message #292986] Thu, 10 January 2008 07:34 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Then what would be the problem?
Please advice.
Re: SQL loader problem [message #292990 is a reply to message #292986] Thu, 10 January 2008 07:42 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
What is NLS_NUMERIC_CHARACTERS option?
Post some more sample data, for which it is working and for which it is not working.

By
Vamsi
Re: SQL loader problem [message #293095 is a reply to message #292986] Thu, 10 January 2008 21:41 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
rajavu1 wrote on Thu, 10 January 2008 18:49


No . Never , unless there is Error .

Thumbs Up
Rajuvan

Yes. It is working for other dates. For one particular date's records, it is rejecting. What would be the error?
The same data is accepted in the another one date.(last field with 0.00). Please advice.

[Updated on: Thu, 10 January 2008 21:44]

Report message to a moderator

Re: SQL loader problem [message #293139 is a reply to message #293095] Fri, 11 January 2008 00:22 Go to previous message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
In the control file
Quote:

asOfDate DATE "MM/DD/YYYY"
In the data it is 12202007.

Changing it to MMDDYYYY helps you? Have a try.

By
Vamsi
Previous Topic: expdp error
Next Topic: External table data location
Goto Forum:
  


Current Time: Wed Nov 13 06:25:35 CST 2024