Using Oracle Heterogeneous Services with two ODBC data sources

Are you evaluating

an ODBC driver?

Yes I am!

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;