Special character being ignored by Oracle Stage

deepa_shenoy · Post by **deepa_shenoy** » Thu May 20, 2010 11:13 pm

Hi,

I have a simple ETL flow.

ODBC Stage--------> ORA Stage

The source has a special character(similar to ' ) which is read by ODBC stage but being replaced by a reverse question mark symbol in the target by the Oracle stage.

How to avoid this?

Thanks in advance.

Deepa

ray.wurlod · Post by **ray.wurlod** » Fri May 21, 2010 1:23 am

Find out what the character actually is and whether it is correctly handled by the locale and map you are using for DataStage, then make sure that the NLS_LANG setting for Oracle is compatible.

Aquilis · Post by **Aquilis** » Fri May 21, 2010 3:38 am

1. For Oracle Enterprise Stage, datatype in DS for the column should be 'NVarchar'.
2. For DRS stage, varchar is ok.
3. Better to keep NLS_MAP & NLS_Language as 'UTF-8 ' in both DS & Oracle respectively.

chulett · Post by **chulett** » Fri May 21, 2010 6:37 am

Don't know how you can suggest that without knowing the characterset of the target Oracle database.

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Tue May 25, 2010 8:43 am

Craig's answer is correct - you must set the character set correctly for BOTH the incoming data and the outgoing data. It is highly doubtful that you can change the Oracle map to UTF8 if that isn't the default, the "ripple effect" of changing the map could be significant. In the US we get spoiled because the default of UTF8 works for the basic US characters.

Please note, that setting both correctly does not insure that the character will be mapped. It may be a case where the character in the source map does not have an equivalent character in the target map. In those cases you'll need to either live with the "?" or manually parse the incoming field and replace the offending characters (something I've had to do at several sites).