Running on DS 22.214.171.124
1) The customer is trying to read from an Oracle DB with NLS_CHARACTERSET set to WE8DEC.
We donu2019t list WE8DEC as a Datastore code page. Do you know if the Oracle clientu2019s NLS_LANG is set to UTF-8 if the data will be transcoded between WE8DEC and UTF-8 as it goes from the Server to the client? If so, then I could set the Datastore code page to UTF-8 and read the data. From what I see on the Web, WE8DEC is a multi-byte code page.
If the above wonu2019t work, what are my options?
2) The customer will have multiple Oracle sources and the NLS_CHARACTERSET is not the same on all servers. Iu2019m only going to have one NLS_LANG setting for the Oracle client on the Job Server machine. Iu2019m hoping that setting NLS_LANG to UTF-8 or AL32UTF8 will cause the data to get transcoded from the server code page to the Oracle client code page during the transmission. Iu2019ll just have to set my Oracle Datastores to UTF-8.
If the above will work, is there a limit to which code pages can be converted automatically to UTF-8? If that doesnu2019t work, the only other option I can think of is to NLS_LANG every time I want to read from a different NLS_CHARACTERSET.
3) Some of the Oracle servers may have different languages or territories. Iu2019m thinking I must change the NLS_LANG setting before I pull from a server with a different language or territory. Is this correct? If so, Iu2019m thinking I could use the set_env() to set the NLS_LANG environment before extracting from a Datastore that needs a specific language. I would only be able to pull from Oracle Datastores with the corresponding language while the environment was set. If I wanted to pull from Datastores with a different code page Iu2019d have to use set_env() again before doing so.
Would this work if I try to change the NLS_LANG setting using set_env() within the same Job? Iu2019m pretty sure the Datastore connections are tested at the beginning of the job, but I donu2019t know if those connections are dropped and then re-established once the Data Flow starts. If new connections are not made when the Data Flow starts, then my assumption is that Iu2019ll be restricted to one Oracle client locale setting for the life of the job.
4) How do you set the Oracle client locale on AIX? Is it simply setting the environment variable NLS_LANG or is there more to it?