Home » RDBMS Server » Server Utilities » External Table - Wrong line number in error log.
External Table - Wrong line number in error log. [message #258112] Fri, 10 August 2007 05:14 Go to next message
rons
Messages: 18
Registered: June 2007
Location: Scottland
Junior Member
Hi,

Oracle Version 9.1.0.7[HP Unix]

I'm having csv file like the following.
12050,120110000000,142011101111,31/07/2007,INR,2752.71,1,160,14.25,201110101111, ,26/12/1964,195.57,0,2752.71,20
11,0,003885384,0,FN,,0,,AHMED,0,2752.71,32.69,0,1,0,01/01/1900,0,01/01/1900,2752.71,,0,4,0,1,499.99,0,0,,0,01/01/1900,0,
15000,01/01/1900,0,0,0,0,0,IN,SB101,130.75,0,0,0,0,0,0,,N,1,0,0,0,2752.71


Above given is a single line of a csv file.

While loading these CSV files using External table, if a line having an error, it reports lineno+5 in the error log.

It is loading and throwing the bad records correctly, only the error log is showing wrong line number. Even it is showing correct error message except line number.
error processing column MARKET_SEGMENT_CD in row 9974 for datafile /home/boietl/BOIDW_FEED_DIR/BOIDW_CBS_DIR/3107072025SBCB.csv
ORA-01722: invalid number

Has anybody come across this situation!!!

Ronald.
Re: External Table - Wrong line number in error log. [message #258198 is a reply to message #258112] Fri, 10 August 2007 09:53 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
ORA-01722:	invalid number
Cause:	The attempted conversion of a character string to a number failed because the character
 string was not a valid numeric literal.
 Only numeric fields or character fields
 containing numeric data may be used in arithmetic functions or expressions. 
Only numeric fields may be added to or subtracted from dates.
Action:	Check the character strings in the function 
or expression. Check that they contain only numbers, a sign, a decimal point, 
and the character "E" or "e" and retry the operation.
Re: External Table - Wrong line number in error log. [message #258549 is a reply to message #258112] Mon, 13 August 2007 01:00 Go to previous messageGo to next message
rons
Messages: 18
Registered: June 2007
Location: Scottland
Junior Member
Hi DreamzZ,

I did not understand what you mean!

Ronald.
Re: External Table - Wrong line number in error log. [message #258555 is a reply to message #258549] Mon, 13 August 2007 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check in row 9974 if the value associated with column MARKET_SEGMENT_CD is a number.

Regards
Michel
Re: External Table - Wrong line number in error log. [message #258675 is a reply to message #258112] Mon, 13 August 2007 06:12 Go to previous message
rons
Messages: 18
Registered: June 2007
Location: Scottland
Junior Member
Hi Michel,

Even I have thought same and wrote all my shell script based on the error log line number.

But on investigation, on some places(not always) the line number shown was "actual_error_line + 5". To check I have deleted the error-prone line in the csv file and re-loaded the csv file again and loaded using external file. It was loaded fine.

I found that, whenever there are too many columns in the csv file this kind of error was reported. Is there any limitation on the line buffer in oracle.(Becuase in vi editor a record wraps to around 3-4 lines).

Regards,
Ronald.
Previous Topic: Problems with DATAPUMP EXP
Next Topic: hanged import
Goto Forum:
  


Current Time: Sat Jun 22 22:24:32 CDT 2024