Easysoft Blog

Tue, 23 May 2017

Using Oracle Heterogeneous Services with Two ODBC Data Sources

If you want to connect Oracle to two ODBC data sources, you need to create two Oracle listeners and two database links, one for each data source. For example:

$ more /etc/odbc.ini

[DSN1]
Driver = SYBASE
...

[DSN2]
Driver = SYBASE
...


$ more $ORACLE_HOME/network/admin/listener.ora

listener =
  (description_list = 
    (description = 
      (address_list =
        (address = (protocol = tcp)(host = unixhost)(port = 1521))
   )
    )
 sid_list_listener=
 (sid_list=
  (sid_desc=
   (sid_name=DSN1)
   (oracle_home=...)
   (program=...)
    )
  (sid_desc=
   (sid_name=DSN2)
   (oracle_home=...)
   (program=...)
    )
  )

$ more $ORACLE_HOME/hs/admin/initDSN1.ora
...
HS_FDS_CONNECT_INFO=DSN1
HS_FDS_SHAREABLE_NAME=...
...


$ more $ORACLE_HOME/hs/admin/initDSN2.ora
...
HS_FDS_CONNECT_INFO=DSN2
HS_FDS_SHAREABLE_NAME=...
...


$ more $ORACLE_HOME/network/admin/tnsnames.ora

DG4ODBC1=
 (description=
  (address=(protocol=tcp)(host=...)(port=1521))
    (connect_data=(sid=DSN1))
    (hs=ok)
 )
DG4ODBC2=
 (description=
  (address=(protocol=tcp)(host=...)(port=1521))
    (connect_data=(sid=DSN2))
    (hs=ok)
 )

$ sqlplus / as sysdba

SQL> create database link mylink1 connect to "user" identified by
"password" using 'DG4ODBC1';
create database link mylink2 connect to "user" identified by
"password" using 'DG4ODBC2';

SQL> select * from "MYTABLE1"@mylink1;
SQL> select * from "MYTABLE2"@mylink2;