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 comes as part of Oracle® 12c / Oracle® 11g, at no additional cost, and supports both these versions of Oracle® and Oracle® 10g.
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 by contacting the Easysoft support team ().
Otherwise, download the 32-bit Sybase ODBC driver for your platform. (Registration required.)
For installation instructions, see the Sybase ODBC driver documentation. Refer to the documentation to see 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 = /usr/local/easysoft/unixODBC/lib/libodbc.so HS_FDS_SUPPORT_STATISTICS = FALSE
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 do not 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.
If 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 do not solve the problem, add one of the following lines to your init file and stop / restart your Oracle® listener
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
–Or–
HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
If setting HS_LANGUAGE
to AMERICAN_AMERICA.WE8ISO8859P1
does not 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;
).
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.