Home » RDBMS Server » Server Utilities » help in fixing Extra space (Oracle 10g)
help in fixing Extra space [message #445291] Sun, 28 February 2010 02:50 Go to next message
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 844 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 messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
u263066 wrote on Sun, 28 February 2010 09:50
ANy 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 #445294 is a reply to message #445293] Sun, 28 February 2010 04:05 Go to previous messageGo to next message
u263066
Messages: 47
Registered: March 2007
Member
Thanks 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
  • Attachment: orafaq.pdf
    (Size: 170.77KB, Downloaded 1892 times)
Re: help in fixing Extra space [message #445296 is a reply to message #445293] Sun, 28 February 2010 04:19 Go to previous messageGo to next message
u263066
Messages: 47
Registered: March 2007
Member
what i notices ^M is coming at the end. This I cann't see from vi and cat editors.

Here is file for reference.
  • Attachment: orafaq.pdf
    (Size: 32.46KB, Downloaded 2128 times)

[Updated on: Sun, 28 February 2010 04:51]

Report message to a moderator

Re: help in fixing Extra space [message #445297 is a reply to message #445296] Sun, 28 February 2010 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dos2unix commnad to remove these ^M and next time transfer your file in binary mode.

Note that many of us (including me) can't or don't want to download files. Posting this way will remove them for those that can answer you.

Regards
Michel
Re: help in fixing Extra space [message #445298 is a reply to message #445297] Sun, 28 February 2010 04:57 Go to previous messageGo to next message
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 #445299 is a reply to message #445297] Sun, 28 February 2010 05:00 Go to previous messageGo to next message
u263066
Messages: 47
Registered: March 2007
Member
i tried to use
:1,$s/^V^M//
but no line i am finding, that confirms there is ^M in data file.




Re: help in fixing Extra space [message #445300 is a reply to message #445294] Sun, 28 February 2010 05:15 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
u263066 wrote on Sun, 28 February 2010 11:05
Thanks 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.
Re: help in fixing Extra space [message #445301 is a reply to message #445300] Sun, 28 February 2010 05:18 Go to previous message
u263066
Messages: 47
Registered: March 2007
Member
thanks flyboy , this is great help

hoping , with these workarounds i can fix this

thanks once again.
Previous Topic: sqlldr and index growth problem
Next Topic: importing from .dmp file taken from different edition
Goto Forum:
  


Current Time: Thu Mar 28 17:39:45 CDT 2024