Home » RDBMS Server » Server Utilities » special character while loading (oracle 11gr2 sun solaris 10.5)
special character while loading [message #596250] Thu, 19 September 2013 08:45 Go to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Hi All,

I have a file which i am loading , the last column is being loaded with special character which looks like sqare shape character for all the rows.
How can i prevent it to load as it is not present in the file

data in file
 
Department|2|Tranport for London|Rail & Underground|ER|ER|ER|ER|ER|ER|555555555|Owner 8|8


the data once loaded for the last column on which is for above example last column value 8 will be loaded with 8 and square share character.

Any clue

icon10.gif  Re: special character while loading [message #596262 is a reply to message #596250] Thu, 19 September 2013 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your file was generated by a Windows (or some big OS) and you do it in Unix.
You have to either transfer your file in ASCII mode or use a program like "dos2unix" to convert the end of line characters.

[Updated on: Thu, 19 September 2013 10:37]

Report message to a moderator

Re: special character while loading [message #596264 is a reply to message #596262] Thu, 19 September 2013 10:51 Go to previous messageGo to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Yes that is the case , but my user will always generate file in window and my which will be seen in unix and loader will read. how can i set the property so that i don't have to convert it
Re: special character while loading [message #596265 is a reply to message #596262] Thu, 19 September 2013 10:58 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Thu, 19 September 2013 16:37

transfer your file in ASCII mode

Re: special character while loading [message #596641 is a reply to message #596250] Wed, 25 September 2013 06:14 Go to previous messageGo to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Hi ,

sorry for late replying,

The solution dos2unix works very well, but i have implemented it differently, the system which produce file had [CR/LF] for end of line which i have changed to just [LF], so i don't have to convert the file.

Many thanks for your suggestion , it was also good to know those.
Re: special character while loading [message #616550 is a reply to message #596641] Wed, 18 June 2014 03:55 Go to previous messageGo to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Dear all ,
sorry for late replying i was out for holiday.

i have used the dos2unix command in external table in preprocessor. so while reading the file it remove the special character on the fly.

ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY PDW_SOURCE
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY NEWLINE 
   PREPROCESSOR PDW_SOURCE:'pdwpws_dos2unix.sh'
   BADFILE PDW_BAD:'PDWLCHTargetFile.bad'
Re: special character while loading [message #616555 is a reply to message #616550] Wed, 18 June 2014 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks to let us know.
Can you post the content of file pdwpws_dos2unix.sh to complete the topic.

Re: special character while loading [message #617215 is a reply to message #616555] Thu, 26 June 2014 03:04 Go to previous messageGo to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
HI Michel

just a one line code
$ cat pdwpws_dos2unix.sh
/usr/bin/dos2unix $1  2>/dev/null


This scripts is being called in the external table preprocessor , which removes the character on the fly when it reads the row
CREATE TABLE EXT_LCH_TARGET
(
  ATTRIBUTED_AREA    VARCHAR2(250 BYTE),
  BUSINESS_UNIT      VARCHAR2(250 BYTE),
  LINEOPS            VARCHAR2(250 BYTE),
  ALLOCATION         VARCHAR2(250 BYTE),
  TARGETMEASURETYPE  VARCHAR2(250 BYTE),
  LCH_TARGET_DATE    VARCHAR2(250 BYTE),
  INTERNAL_TARGETS   VARCHAR2(250 BYTE),
  EXTERNAL_TARGETS   VARCHAR2(250 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY PDW_SOURCE
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY NEWLINE 
   PREPROCESSOR PDW_SOURCE:'pdwpws_dos2unix.sh' ---------------used here
   BADFILE PDW_BAD:'PDWLCHTargetFile.bad'
   LOGFILE PDW_LOG:'PDWLCHTargetFile.log'
   skip 1 
   FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
Re: special character while loading [message #617220 is a reply to message #617215] Thu, 26 June 2014 03:53 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks for sharing. A good example of preprocessing the file to covert the file mode and then load it at one go.
Previous Topic: EXPDP hangs
Next Topic: sql loader data isse
Goto Forum:
  


Current Time: Mon Mar 18 23:48:39 CDT 2024