Home » Infrastructure » Unix » Data loading from unix to oracle
Data loading from unix to oracle [message #290260] Fri, 28 December 2007 04:09 Go to next message
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.

Re: Data loading from unix to oracle [message #290262 is a reply to message #290260] Fri, 28 December 2007 04:17 Go to previous messageGo to next message
Littlefoot
Messages: 19630
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.
Re: Data loading from unix to oracle [message #290264 is a reply to message #290260] Fri, 28 December 2007 04:19 Go to previous messageGo to next message
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.
Re: Data loading from unix to oracle [message #290268 is a reply to message #290264] Fri, 28 December 2007 04:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
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
Re: Data loading from unix to oracle [message #290291 is a reply to message #290287] Fri, 28 December 2007 05:08 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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?
Re: Data loading from unix to oracle [message #290296 is a reply to message #290292] Fri, 28 December 2007 05:30 Go to previous messageGo to next message
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 228 times)
Re: Data loading from unix to oracle [message #290299 is a reply to message #290296] Fri, 28 December 2007 05:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: Data loading from unix to oracle [message #290306 is a reply to message #290303] Fri, 28 December 2007 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I want to increase the errors allowed.
Is it possible?


Yes it is possible and explained in http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/toc.htm

Regards
Michel
Re: Data loading from unix to oracle [message #290317 is a reply to message #290306] Fri, 28 December 2007 06:14 Go to previous message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Thanks a lot Michel,Littlefoot and MarcS. It is working.

Smile

[Updated on: Fri, 28 December 2007 06:17]

Report message to a moderator

Previous Topic: Cannot load module libjox9.a(shr.o).
Next Topic: INPUT FROM SHELL SCRIPT
Goto Forum:
  


Current Time: Sun Sep 21 19:19:23 CDT 2014

Total time taken to generate the page: 0.08497 seconds