Hi,
We are trying to read data from some oracle source databases and load it into our staging area. There are no transformations being applied, it's a on to one mapping. The target tables are also oracle.
When executing this job we are encountering some warnings while trying to read data.
"Invalid character(s) ([xA3]) found converting string (code point(s): XXX [xA3]10M ) from codepage UTF-8 to Unicode, substituting. [nls/converter.C:1090]"
The data in one of the rows is "XXX <pound_symbol>10M"
The project deafult setting for NLS is UTF-8.
We find that in the DB we have special characters like the "pound" symbol. We tried by giving ISO 8859-9:1989 setting on the source oracle enterprise stage, in which was also in vain.
Could some one please address this issue?
NLS Settings in DataStage
Moderators: chulett, rschirm, roy
What stage are you using for Oracle and what are your NLS environment settings for Oracle?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-> Source DB NLS setting: (select * from nls_database_parameters)
NLS_NCHAR_CHARACTERSET =AL16UTF16
-> Target DB NLS Setting: (select * from nls_database_parameters)
NLS_NCHAR_CHARACTERSET AL16UTF16
-> In the Datastage job:
ORCL_SRC_STG ---> XFM ----> ORCL_INSERT_STG
I'm using using oracle enterprise stage to read data. NLS Setting for the stage = UTF-8 (project default)
I have configured the target oracle enterprise stage as follows:
Upsert Mode = User-defined UpdateOnly
UPDATE SQL = INSERT INTO STG_TBL ....
There are 5 records from the source which have spl characters and 4 are getting inserted into the target, except one. The error I get is "Unable to use a record for update"
The staging table that I'm trying to insert into doesn't have any constraints on it.
-Deepti
NLS_NCHAR_CHARACTERSET =AL16UTF16
-> Target DB NLS Setting: (select * from nls_database_parameters)
NLS_NCHAR_CHARACTERSET AL16UTF16
-> In the Datastage job:
ORCL_SRC_STG ---> XFM ----> ORCL_INSERT_STG
I'm using using oracle enterprise stage to read data. NLS Setting for the stage = UTF-8 (project default)
I have configured the target oracle enterprise stage as follows:
Upsert Mode = User-defined UpdateOnly
UPDATE SQL = INSERT INTO STG_TBL ....
There are 5 records from the source which have spl characters and 4 are getting inserted into the target, except one. The error I get is "Unable to use a record for update"
The staging table that I'm trying to insert into doesn't have any constraints on it.
-Deepti
What are your runtime environment settings for NLS_LANG and ORA_NLS?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
I checked for the NLS_LANG in the dsenv file
NLS_LANG = American_America.WE8ISO8859P1
and the source and target DB have the following NLS parameters
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8ISO8859P1
I'm not sure where I need to check for the ORA_NLS? It's not there in the dsenv file.
I tried giving the NLS setting of ISO_8859-1:1987 in both the source and target oracle enterprise stage. But, it still seems to be throwing the same error "Unable to use a record for update"
NLS_LANG = American_America.WE8ISO8859P1
and the source and target DB have the following NLS parameters
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8ISO8859P1
I'm not sure where I need to check for the ORA_NLS? It's not there in the dsenv file.
I tried giving the NLS setting of ISO_8859-1:1987 in both the source and target oracle enterprise stage. But, it still seems to be throwing the same error "Unable to use a record for update"
Your data is stored in UTF16 in the database so it would be best to set your NLS_LANG to the same and also specify that in your Oracle stage.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
??deepticr wrote:-> Source DB NLS setting: (select * from nls_database_parameters)
NLS_NCHAR_CHARACTERSET =AL16UTF16
-> Target DB NLS Setting: (select * from nls_database_parameters)
NLS_NCHAR_CHARACTERSET AL16UTF16
...
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>