OraFAQ Forum: Server Utilities » SQL loader problem

Home » RDBMS Server » Server Utilities » SQL loader problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL loader problem [message #292795]

Wed, 09 January 2008 21:10

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.

Attachment: positions.ctl.txt
(Size: 1.80KB, Downloaded 1192 times)

[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

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

Report message to a moderator

Re: SQL loader problem [message #292832 is a reply to message #292828]

Thu, 10 January 2008 00:21

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.

Report message to a moderator

Re: SQL loader problem [message #292859 is a reply to message #292795]

Thu, 10 January 2008 01:24

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Is there sufficient fields in your line?

Regards
Michel

Report message to a moderator

Re: SQL loader problem [message #292863 is a reply to message #292859]

Thu, 10 January 2008 01:30

vanathi
Messages: 74
Registered: December 2007
Location: India

Member

Yes. I am getting.

Please read the attachment.

Attachment: Clipboard-xls.xls
(Size: 18.00KB, Downloaded 1338 times)

Report message to a moderator

Re: SQL loader problem [message #292868 is a reply to message #292863]

Thu, 10 January 2008 01:36

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I can't download xls file.

Regards
Michel

Report message to a moderator

Re: SQL loader problem [message #292871 is a reply to message #292868]

Thu, 10 January 2008 01:47

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.

Report message to a moderator

Re: SQL loader problem [message #292872 is a reply to message #292863]

Thu, 10 January 2008 01:52

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Is it 0.00,
or 0.00

By
Vamsi

Report message to a moderator

Re: SQL loader problem [message #292874 is a reply to message #292872]

Thu, 10 January 2008 01:54

vanathi
Messages: 74
Registered: December 2007
Location: India

Member

It is 0.00
If no data in the last field, then accepts.

Report message to a moderator

Re: SQL loader problem [message #292877 is a reply to message #292874]

Thu, 10 January 2008 02:00

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

Report message to a moderator

Re: SQL loader problem [message #292881 is a reply to message #292877]

Thu, 10 January 2008 02:04

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.

Report message to a moderator

Re: SQL loader problem [message #292887 is a reply to message #292881]

Thu, 10 January 2008 02:12

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Oops! I didn't notice that.

By
Vamsi

Report message to a moderator

Re: SQL loader problem [message #292986 is a reply to message #292795]

Thu, 10 January 2008 07:19

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

i.rajavu@gmail.com

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

Thumbs Up

Rajuvan

Report message to a moderator

Re: SQL loader problem [message #292987 is a reply to message #292986]

Thu, 10 January 2008 07:34

vanathi
Messages: 74
Registered: December 2007
Location: India

Member

Then what would be the problem?
Please advice.

Report message to a moderator

Re: SQL loader problem [message #292990 is a reply to message #292986]

Thu, 10 January 2008 07:42

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

Report message to a moderator

Re: SQL loader problem [message #293095 is a reply to message #292986]

Thu, 10 January 2008 21:41

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

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

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

Report message to a moderator

Previous Topic:	expdp error
Next Topic:	External table data location

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 06:38:53 CDT 2024