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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #258675 is a reply to message #258112] |
Mon, 13 August 2007 06:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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.
|
|
|
Goto Forum:
Current Time: Thu Sep 12 04:36:38 CDT 2024
|