How do I connect Oracle to Sybase Adaptive Server Enterprise (ASE)?

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.

  1. Check whether your version of DG4ODBC is 32-bit or 64-bit: 
    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.

  2. Install, license, and test the Sybase ODBC driver on the machine where DG4ODBC is installed.

    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).

  3. Create a DG4ODBC init file. For example: 
    cd $ORACLE_HOME/hs/admin
cp initdg4odbc.ora initsybase.ora
  4. Ensure these parameters and values are present in your init file: 
    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
  5. Add an entry to $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/.

  6. Add a DG4ODBC entry to $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)
 )
  7. Start (or restart) the Oracle Listener: 
    cd $ORACLE_HOME/bin
./lsnrctl stop
./lsnrctl start
  8. Connect to your Oracle database in SQL*Plus.
  9. In SQL*Plus, create a database link for the target Sybase database. For example: 
    CREATE PUBLIC DATABASE LINK sybaselink
CONNECT TO "dbuser" IDENTIFIED BY "dbpassword" USING 'sybase_connection';

    Replace dbuser and dbpassword with a valid user name and password for the target Sybase database.

NCHAR and NVARCHAR data

If you get text corruption when using DG4ODBC to retrieve NCHAR or NVARCHAR data:

  1. Add the following lines to your init file: 
    HS_RPC_FETCH_REBLOCKING = OFF
HS_FDS_FETCH_ROWS = 1
  2. Stop and then restart your Oracle listener.

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;).

Further information