Page 1 of 1
Special character being ignored by Oracle Stage
Posted: Thu May 20, 2010 11:13 pm
by deepa_shenoy
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
Posted: Fri May 21, 2010 1:23 am
by ray.wurlod
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.
Posted: Fri May 21, 2010 3:38 am
by Aquilis
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.
Posted: Fri May 21, 2010 6:37 am
by chulett
Don't know how you can suggest that without knowing the characterset of the target Oracle database.
Posted: Tue May 25, 2010 8:43 am
by asorrell
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).