Oracle Database Gateway Characterset Problem

Problem

Environment: 11.1 Oracle Database Gateway

We are replacing, and consolidating our Oracle Database Gateway environment.

And after some time passed, we had encountered characterset problem.

- I will call gateway and gw instead of "Oracle Database Gateway" in the rest of this post.

While issuing select from remote sybase db table via new gateway,
we are seeing strange characters instead of Turkish characters.

Cause

After investigating the problem, we found that NLS_LANG environment,
variable in former gateway is different from the new gateway environment.

Solution

There is two alternative way of solving this problem:

First alternative:

Gateway is a listener, and only process you can see associated with listener is:

oracle@gw:>;;ps -ef|grep inherit|grep _MYDB
oracle 21758314 1 0 Sep 13 pts/0 0:01 /u01/app/oracle/product/11.1.0/bin/tnslsnr GW_MYDB_1664 -inherit

So, changing NLS_LANG parameter before starting listener solves the problem.
But, this is not a clean solution, because you need to know and deal with the environment variable before starting the listener.
Putting this to oracle user profile file "oracle@gw:/home/oracle/.profile" does not work for our situation,
because this is a consolidated gateway environment
and there exists other gateway definitions for other oracle to non-oracle databases already configured and running.

Second alternative:

While connecting to Sybase side, there is a parameter file influencing the sybase driver used by the listener.
After adding the HS_LANGUAGE parameter as below it solved the character set problem.

HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9

Also, there is a lesson while making the trials for seeing the character problem.
It is, after changing the initMYDB.ora parameter file, and stop/starting the gateway listener,
you must close database link and issue the select statement after then.
If you do not close the database link by below SQL command,
it still uses the old initMYDB.ora based configuration.

select * from dual;

commit;

alter session close database link DG_NEMS_PRE;

SELECT * FROM "dbo"."my_table1"@DG_MYDB_LIVE;

Gateway parameter file for sybase is like below:

/u01/app/oracle/product/11.1.0/dg4sybs/admin/initMYDB.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for Sybase

HS_FDS_CONNECT_INFO=10.10.44.22:4100/mydb1
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_STRING="EnableSPColumnTypes=2"
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM
HS_FDS_TRACE_FILE_NAME = /home/oracle/derya/dg4sybs_mydb1.trc
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
#
# Environment variables required for Sybase
#
set SYBASE=/u01/app/sybase
set LIBPATH=/u01/app/sybase/OCS-12_5/lib:/u01/app/sybase:/u01/app/oracle/product/11.1.0/lib
HS_FDS_QUOTE_IDENTIFIER=FALSE

Listener.ora entry for the service, given for informational purpose, not really needed for this case:

GW_MYDB_1664 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.2.20) (PORT = 1664))
)
)
)
SID_LIST_GW_MYDB_1664 =
(SID_LIST =
(SID_DESC =
(SID_NAME = MYDB1)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0)
(PROGRAM = dg4sybs)
(ENVS=LIBPATH=/u01/app/oracle/product/11.1.0/dg4sybs/driver/lib:/u01/app/oracle/product/11.1.0/lib)
)
)

 

You may check for gateway configuration from a former post. This post covers configuration steps: from target sybase database  gateway parameter file creation to source oracle database dblink creation. 

http://deryaoktay.wordpress.com/2012/05/22/defining-database-link-by-using-oracle-gateway/