help in fixing Extra space [message #445291] |
Sun, 28 February 2010 02:50 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
u263066
Messages: 47 Registered: March 2007
|
Member |
|
|
I have one issue while loading the value through sql*loader
the last column data is SG1 and whn its loaded , it is length of this columns is showing 4 char.
Unable to understand, how to find this extra space. Though used TRIM but does not work.
ANy better options, to handle this senarios
-
Attachment: IBX.JPG
(Size: 33.32KB, Downloaded 985 times)
[Updated on: Sun, 28 February 2010 03:04] Report message to a moderator
|
|
|
Re: help in fixing Extra space [message #445293 is a reply to message #445291] |
Sun, 28 February 2010 03:34 ![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) |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
u263066 wrote on Sun, 28 February 2010 09:50ANy better options, to handle this senarios
As you did not post table definition, control file and some sample data, it is hardly possible to guess.
Most probably, the IBX column contains other blank character than space. You may get the exact content of the IBX column using DUMP function. SELECT DUMP(ibx) FROM <table_name>;
|
|
|
|
|
|
Re: help in fixing Extra space [message #445298 is a reply to message #445297] |
Sun, 28 February 2010 04:57 ![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) |
u263066
Messages: 47 Registered: March 2007
|
Member |
|
|
thanks Michel , but i guess its this is only added at the end of last columns. How should i confirm this is only ^M in file. I just got this while pulling the data with DUMP(col_name, 1017), but could not really confirm this is ^M, the reason, opening the same file is not showing the ^M.
More over, the file is being pushed by some other file, directly by the user, so DOS2Unix is not really helps. ANy better options.
|
|
|
|
Re: help in fixing Extra space [message #445300 is a reply to message #445294] |
Sun, 28 February 2010 05:15 ![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) |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
u263066 wrote on Sun, 28 February 2010 11:05Thanks flyboy,
dump is not really help to root cause.
I am attaching the defination and other information in attachment, that probally it will help you to give some input.
Thanks in advance
DUMP is very great help in identifying the cause. It shows, that the last character in IBX column is CHR(13). As end-of-line in Windows is represented by CHR(13)CHR(10), while in Unix/Linux it is only CHR(10), it seems you are trying to import Windows file on Unix/Linux.
There are two ways how to handle this - replace Windows end-of-lines with Unix/Linux ones (dos2unix utility should do it - how did you use it?) or treat it in sqlldr. These threads seem to have some clues:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4972732303253
http://www.dbforums.com/oracle/1003160-sqlldr-end-line.html
I have never done this, so I cannot confirm it. Just try it.
|
|
|
|