If your Oracle database is running on Linux or UNIX, you can use Oracle's Database Gateway for ODBC (DG4ODBC) and the Sybase ODBC driver to connect Oracle to Sybase Adaptive Server Enterprise (ASE) and Express.
DG4ODBC interacts with Heterogeneous Services (an Oracle database component) to allow Oracle client applications to access non-Oracle databases. The non-Oracle data is transparently integrated, and so Oracle client applications are not aware that the data is stored in a remote database from another vendor.
The following instructions show you how to connect Oracle with Sybase ASE. For further information about DG4ODBC, refer to our DG4ODBC tutorial.
cd $ORACLE_HOME/bin file dg4odbc
If the file
command's output contains "ELF 64-bit LSB executable," or something similar, DG4ODBC is 64-bit, and you need to obtain a 64-bit version of the Sybase ODBC driver for your platform.
Otherwise, download the 32-bit Sybase ODBC driver for your platform.
For installation instructions, refer to the Sybase ODBC driver documentation. Refer to the documentation to find out which environment variables you need to set (LD_LIBRARY_PATH
, LIBPATH
, LD_RUN_PATH
, or SHLIB_PATH
depending on the platform and linker).
cd $ORACLE_HOME/hs/admin cp initdg4odbc.ora initsybase.ora
HS_FDS_CONNECT_INFO = my_sybase_odbc_dsn HS_FDS_SHAREABLE_NAME = installation_dir/easysoft/unixODBC/lib/libodbc.so HS_FDS_SUPPORT_STATISTICS = FALSE
Replace installation_dir
with the directory under which you installed the Sybase ODBC driver, the default location is /usr/local
. Replace my_sybase_odbc_dsn with name of a Sybase ODBC driver data source that connects to the target Sybase database.
The HS_FDS_SUPPORT_STATISTICS
parameter controls whether Oracle gathers statistics from the Sybase database at the same time as trying to query an ODBC connection. Sybase ASE does not support this behaviour. If you don't set the HS_FDS_SUPPORT_STATISTICS
parameter to False
, you may get an error similar to:
General error: connection is busy with results of another hstmt
$ORACLE_HOME/network/admin/listener.ora
that creates a SID_NAME
for DG4ODBC. For example:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC= (SID_NAME=sybase) (ORACLE_HOME=oracle_home_directory) (PROGRAM=dg4odbc) (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib: /usr/local/easysoft/lib) ) )
Replace oracle_home_directory
with the value of $ORACLE_HOME
. For example, /u01/app/oracle/product/11.2.0/dbhome_1/
.
$ORACLE_HOME/network/admin/tnsnames.ora
that specifies the SID_NAME
created in the previous step. For example:
sybase_connection= (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521)) (CONNECT_DATA= (SID=sybase)) (HS=OK) )
cd $ORACLE_HOME/bin ./lsnrctl stop ./lsnrctl start
CREATE PUBLIC DATABASE LINK sybaselink CONNECT TO "dbuser" IDENTIFIED BY "dbpassword" using 'sybase_connection';
Replace dbuser
and dbpassword
with a valid username and password for the target Sybase database.
NCHAR
and NVARCHAR
dataIf you get text corruption when using DG4ODBC to retrieve NCHAR
or NVARCHAR
data:
HS_RPC_FETCH_REBLOCKING = OFF HS_FDS_FETCH_ROWS = 1
If these settings don't solve the problem, add one of the following lines to your init file. Then stop and restart your Oracle listener.
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
–Or–
HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
If setting HS_LANGUAGE
to AMERICAN_AMERICA.WE8ISO8859P1
doesn't solve the problem, try setting HS_LANGUAGE
to AMERICAN_AMERICA.US7ASCII
.
Text corruption for NCHAR
or NVARCHAR
data can occur when the Oracle database character set (NLS_CHARACTERSET
parameter value) is AL32UTF8
. (To check what the database character set is, run this query select * from nls_database_parameters;
).