OraFAQ Forum: Unix » Data loading from unix to oracle

Home » Infrastructure » Unix » Data loading from unix to oracle

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

Data loading from unix to oracle [message #290260]

Fri, 28 December 2007 04:09

vanathi
Messages: 74
Registered: December 2007
Location: India

Member

Hi,
I am loading data from .csv files to oracle table everyday.
But now I couldnt load the data. The control file is not reading the data it seems.
Upto yesterday it was working(the same file).

Please advice.

Thanks in advance.

Report message to a moderator

Re: Data loading from unix to oracle [message #290262 is a reply to message #290260]

Fri, 28 December 2007 04:17

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Something changed since yesterday. Check the SQL*Loader log file to see why all those records have been rejected. Once you know that, it should be easy to fix it.

Report message to a moderator

Re: Data loading from unix to oracle [message #290264 is a reply to message #290260]

Fri, 28 December 2007 04:19

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

So, no error(s)? No log-file? No sample control-file? No sample data-file?

Some of us are indeed Oracle-experts and some even guru.
But none of us is clearvoyant Wink

I suspect you are using SQL*Loader?
What's in the log-file?
What was the output from the sql*loader process?

If we don't have this info, we can not advice you in any way.

Report message to a moderator

Re: Data loading from unix to oracle [message #290268 is a reply to message #290264]

Fri, 28 December 2007 04:25

vanathi
Messages: 74
Registered: December 2007
Location: India

Member

Yes. I know. Thats why I have submitted my question here.

Totally 76000 records in the csv file. using sql loader only.
But it just reads 75 datas and rejects 51 from them.
only 5 records are going into table. I referred the log file.
I didnt change the control file and anything.
It is not reading the datas.

[Updated on: Fri, 28 December 2007 04:26]

Report message to a moderator

Re: Data loading from unix to oracle [message #290287 is a reply to message #290268]

Fri, 28 December 2007 04:56

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

Senior Member
Account Moderator

If nothing changed then same things happen.
If different things happen then something changes.
Investigate what?
For instance, why the rows are rejected.

Regards
Michel

Report message to a moderator

Re: Data loading from unix to oracle [message #290291 is a reply to message #290287]

Fri, 28 December 2007 05:08

vanathi
Messages: 74
Registered: December 2007
Location: India

Member

the control file

load data
append
into table PARTCORE3
fields terminated by "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
CONTACTCODE CHAR,
RELATIONSHIPTYPE CHAR,
PORTFOLIOCODE CHAR,
PROCESSED CONSTANT "N",
ID__ "db_seq_PART.nextval"
)

It was working fine only. It was rejecting correctly.
But now it is rejecting correct datas also.

[Updated on: Fri, 28 December 2007 05:11]

Report message to a moderator

Re: Data loading from unix to oracle [message #290292 is a reply to message #290291]

Fri, 28 December 2007 05:14

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

If I'm not mistaken the reason of rejection is noted in the log-file (been a while since I used sql*loader).
So: may we know what's in the log-file? Or should we guess?

Report message to a moderator

Re: Data loading from unix to oracle [message #290296 is a reply to message #290292]

Fri, 28 December 2007 05:30

vanathi
Messages: 74
Registered: December 2007
Location: India

Member

Log file is attached.

wc -l webposn.csv gives
76160 webposn.csv

I was using the same data.
But now it was not working.

The control file I gave, is sample template.

Attachment: log.txt
(Size: 9.02KB, Downloaded 1900 times)

Report message to a moderator

Re: Data loading from unix to oracle [message #290299 is a reply to message #290296]

Fri, 28 December 2007 05:38

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

Strange: all you need to know is withing that logfile Shocked

Errors allowed: 50

When the number of errors reach 50 SQL*loader just stops

MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table POSCORE3:
  5 Rows successfully loaded.
  51 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

5 rows have been loaded, 51 not because they had data errors.

Record 1: Rejected - Error on table POSCORE3, column CLIENTID.
ORA-12899: value too large for column "PMRDEV"."POSCORE3"."CLIENTID" (actual: 9, maximum: 8)

Record 2: Rejected - Error on table POSCORE3, column LTREPGL.
ORA-01722: invalid number

Check your data and see if it still valid.

Let me aks you another question: do you run it on a Windows-server?
I guess so, because you try to load a *.csv file.

Check the content of the *.csv file and see whether the field seperator is still a comma (,) and not a semicolon ( ; )
Which happens if International Settings has changes.

[Updated on: Fri, 28 December 2007 05:40]

Report message to a moderator

Re: Data loading from unix to oracle [message #290303 is a reply to message #290299]

Fri, 28 December 2007 05:45

vanathi
Messages: 74
Registered: December 2007
Location: India

Member

Thank you friend.

I thought that the no of errors displayed is limited as 50.

As per your reply, If I am getting errors more than 50, It will stop. Ok...
I want to increase the errors allowed.
Is it possible?
If so, tell me.

I will get the csv files only. I checked the datas also.
It is not doing that for that day's data only.
I loaded the next day's data. It is working. But it is also stops after 50 errors.

[Updated on: Fri, 28 December 2007 05:49]

Report message to a moderator