String conversion

[qutesanju]

i have inut column coming from db2 as char40

and i mapped to oracle target table as varchar35
however in oracle it gets displayed as ascii character or some junk charater
any idea which string conversion function to use?
my understanding is that it should display at least 35 charaters,but surprising to see its getting displayed asscii or some junk character

[ray.wurlod]

There is no such thing as "some junk character" in ETL. Every character in the data is part of your client's data, and you have no authority to change that.

You must process the data using an NLS character map that can handle every character in the data.

[qutesanju]

i never said some junk character is part of ETL,i know its coming from clients data
i m investigating why
and we have no authority to change it but we can convert it atleast using some string functions

in input i can see its coming as correct.however in ooutput its displaying as

Code: Select all

 square square square square

[ray.wurlod]

That's not enough. Square is simply how your data viewer is displaying a character that it doesn't know how to display.

You have to discover what these characters are before you can do anything about them. One way to do this is to write the data to a text file (maybe using DB2 export facility) then use the od -x command, which dumps all the characters in their hexadecimal ASCII (or Unicode) equivalents.

Incidentally, by "in ETL" I really meant "in the practice of ETL".

You have told us nothing of your job design, nor of the character set (code page) applied to your target Oracle table.

[qutesanju]

i put output in sequencial fine also,but there also its getting displayed as Square square square square

[chandra.shekhar@tcs.com]

@qutesanju
Those "squares" are because of char datatype and are actually blanks which are padded to the fixed length if the data has a shorter value.
The only solution to this problem is changing the data type to varchar.

[ray.wurlod]

Or you could change APT_STRING_PADCHAR to 0x20.