How do I work with SQL Server VARCHAR(MAX), GUID, and TEXT columns when using Oracle Heterogeneous Services?

The SQL Server ODBC driver allows Oracle to connect to SQL Server by using the generic Heterogeneous Services agent DG4ODBC.

The SQL Server ODBC driver supports all SQL Server data types. DG4ODBC supports data types that are common to most ODBC drivers, but not some of the SQL Server specific ones. If the SQL Server tables that you intend to access from Oracle contain the following data types, you will need to configure your SQL Server ODBC driver data source before connecting.

Data type Notes
VARCHAR(MAX) DG4ODBC does not support this data type. To work around this, add this line to your SQL Server ODBC driver data source: 
VarMaxAsLong = Yes
GUID DG4ODBC does not support this data type. To work around this, add this line to your SQL Server ODBC driver data source: 
DisguiseGuid = Yes
TEXT Although Oracle supports this type, you can only have one TEXT column per table. If you need to limit the length returned by the SQL Server ODBC driver when describing TEXT columns, add this line to your data source: 
LimitLong = num

where num is the maximum length in bytes the SQL Server ODBC driver will return. This is optional and only set this if you have issues with very large TEXT columns.

Accessing SQL Server data from Oracle

To give you some context as to where configuring the SQL Server ODBC driver data source fits into the process, the following steps show you how to connect Oracle to SQL Server by using Heterogeneous Services.

  1. Create and test a SQL Server ODBC data source that connects to the target SQL Server database. For example: 
    [SQLSERVER_SAMPLE] 
Driver=Easysoft ODBC-SQL Server 
Description=SQL Server DSN 
Server=mymssqlmachine\myinstance 
Logging=no
LogFile= /tmp/sqlsrv.log
Mars_Connection=Yes

    Note Mars_Connection must be set to Yes, if you're connecting to SQL Server 2005 or later.

  2. Log in as the Oracle user and set your library path in your .profile or .bash_profile files. For example: 
    LD_LIBRARY_PATH=/usr/local/easysoft/lib:/usr/local/easysoft/unixODBC/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH

    Note On HP-UX, replace LD_LIBRARY_PATH with SHLIB_PATH. On AIX, replace LD_LIBRARY_PATH with LIBPATH.

    If you want to use isql to verify the connection to SQL Server whilst logged in as the Oracle user, add the following entry as well:

    PATH=/usr/local/easysoft/unixODBC/bin:$PATH
export PATH
  3. Either log in again as the Oracle user or run: 
    ./.bash_profile
  4. In your $ORACLE_HOME/hs/admin, create an initsid.ora file. The sid name will be used throughout the connection setup. In our test case, we created initsqlsrv.ora, which has the sid name of sqlsrv.
  5. Our initsqlsrv.ora file had the following contents: 
    # 
# HS init parameters 
# 
HS_FDS_CONNECT_INFO = SQLSERVER_SAMPLE 
#HS_FDS_TRACE_LEVEL = 4 
HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.so 
HS_NLS_NCHAR=UCS2 
#HS_FDS_SUPPORT_STATISTICS=FALSE
# 
# ODBC specific environment variables 
# 
set ODBCINI=/etc/odbc.ini
    Parameter Description
    HS_FDS_CONNECT_INFO This is the name of your Easysoft ODBC data source in your /etc/odbc.ini file.
    HS_FDS_TRACE_LEVEL Un-comment this line if you want to turn on Oracle initsid.ora tracing.
    HS_FDS_SHAREABLE_NAME This is the path to the unixODBC Driver Manager.
    HS_NLS_NCHAR The HS_NLS_NCHAR parameter value tells DG4ODBC to pass UCS-2 encoded data to the Unicode ODBC APIs, rather than UTF-8, which is the default for some versions of DG4ODBC.
    HS_FDS_SUPPORT_STATISTICS This needs to be un-commented if you are using SQL Server 7 or 2000.

    AIX You need to extract the libodbc.so shared object from the libodbc.a file. As the user that installed the SQL Server ODBC driver, run:

    $ cd /usr/local/easysoft/unixODBC/lib
$ ar -X32_64 -xv libodbc.a
x - libodbc.so.1
$ mv libodbc.so.1 libodbc.so
  6. Configure the Oracle listener.ora file, which is usually located in $ORACLE_HOME/network/admin. For example: 
    SID_LIST_LISTENER = 
 (SID_LIST = 
   (SID_DESC= 
     (SID_NAME=sqlsrv) 
     (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1/) 
     (PROGRAM=dg4odbc) 
     (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib: 
            /usr/local/easysoft/lib) 
   ) 
 )
    Parameter Description
    SID_NAME This is the sid part of the initsid.ora file you created.
    ORACLE_HOME Your $ORACLE_HOME value.
    LD_LIBRARY_PATH The path to the libraries included in the SQL Server ODBC driver distribution. On HP-UX, replace LD_LIBRARY_PATH with SHLIB_PATH. On AIX, replace LD_LIBRARY_PATH with LIBPATH.
  7. Edit tnsnames.ora to tell Oracle which server to attach the the listener entry to. For example: 
    sqlsrv_ptr= 
 (DESCRIPTION= 
   (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)) 
   (CONNECT_DATA= (SID=sqlsrv)) 
   (HS=OK) 
 )
    Entry Description
    sqlsrv_ptr You can call this anything allowed by Oracle. This is the name you will use when you create a database link.
    HOST The machine name of your Oracle server.
    PORT The port your Oracle server is listening on.
    SID This must be the SID name specified in your listener.ora file.
  8. Restart the Oracle listener: 
    $ lsnrctl stop
$ lsnrctl start
  9. You can now test that the Oracle configuration is correct by using tnsping: 
    $ tnsping sqlsrv

    If you get an error at this point there is something wrong with your Oracle configuration. Be aware that this only tests the configuration of Oracle and not that Oracle can connect to Easysoft.

  10. Create a link from your Oracle database to the tnsnames.ora entry: 
    CREATE PUBLIC DATABASE LINK "ODBC"
CONNECT TO "test" IDENTIFIED BY VALUES 'test_pass' USING 'sqlsrv_ptr';

    Note the double quote (") and single quote (') enclosed values must be enclosed in the same way when you specify your link in Oracle.

    Value Notes
    ODBC The link name that you will use in your SQL statements.
    test The SQL Server user name.
    test_pass The SQL Server password.
    sqlsrv_ptr The name of the tnsnames.ora entry
  11. To use the link from Oracle, get the name of a SQL Server table and run: 
    SELECT * FROM table_name@link;

Problems accessing your data

If you run into any issues trying to read or write data to SQL Server from Oracle, do the following:

  1. Make sure the issue is not with the application you are using, for example SQL Developer, Toad and so on. Test the issue using SQL*Plus on the Oracle machine. If the issue only happens for example under Toad and not in SQL*Plus, report the issue to the people that support Toad.
  2. Try to limit the issue to the problem column or table. Let's say, for example, you're running a select * from table@link and you have only 1 column that's causing the problem, try running select column from table@link to find out if that gives the same error. This helps us to diagnose the issue.
  3. Turn tracing on within your $ORACLE_HOME/hs/admin/initsid.ora file.
  4. Stop and start your Oracle listener.
  5. Reproduce the issue in SQL*Plus. If you don't get an Oracle trace file in your $ORACLE_HOME/hs/log folder your listener has not been restarted or Oracle has not been configured correctly.
  6. Send the Easysoft Support team ( ):
    1. A copy of your Oracle initsid log file. Zip or compress this file if it's more than 1 MB.
    2. A copy of your initsid.ora file.
    3. A copy of the files ending in _install.info from /usr/local/easysoft.
    4. The version of SQL Server you're connecting to.
    5. The SQL used to generate the table in SQL Server you're accessing. If you're accessing a view, send the SQL used to create the view and tables and views that access it.

    Once we have all items, the Easysoft Support team should be able to quickly work out if this is an Oracle configuration issue or bug, limitation in Oracle, Easysoft configuration or bug, or if we simply need more information.