Partial answer (select SQL Server tables from Oracle):
On the Oracle side, you can create a database link to view data in SQL Server. Since this is a 5 step process, I've also included some debug steps that I use when diagnosing db links from SQL Server in order to track down where the failure is.
On the Oracle machine:
1) Set up an ODBC connection to SQL Server
start => control panel => Administrative tools => Data Sources (ODBC) => System DSN => Add => SQL Server)
Debug: Press the Test Connection button from the ODBC connection window
2) Create an HS file that points to the ODBC connection
In %oracle_home%\hs\admin, create a new file called init<name>.ora. A typical HS file usually just contains:
HS_FDS_CONNECT_INFO = <name of the ODBC connection name>
HS_FDS_TRACE_LEVEL = OFF
Debug: change HS_FDS_TRACE_LEVEL = ON, then check for the appropriate trace files in %oracle_home%\hs\trace
3) Add an entry to your listener.ora
If there is already a SID_DESC present in SID_LIST_LISTENER you can add a new one just below it.
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC=
(SID_NAME=...)
(ORACLE_HOME=...)
(PROGRAM=...)
)
(SID_DESC=
(SID_NAME=<name of the ODBC connection>)
(ORACLE_HOME=<full path for oracleHome>)
(PROGRAM=dg4odbc)
)
)
Debug: Check the listener log
c:> lsnrctl reload
4) Add a TNSEntry
<Name> =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=<Host>)(PORT=<port>))
(CONNECT_DATA= (SID=<ODBC connection name>))
(HS=OK)
)
Debug c:\> tnsping <tns entry name>
5) Create the db link
create database link <name> connect to DUMMY identified by " " using '<tns entry name>';
Debug: SQL> select * from dual@<dblinkname>;
pre 10g, we used to be able to simply create database link <name> using '<tns entry>'; but the connect as identified by section became required starting in 11g even though whatever you enter isn't used (since it uses the login credentials for SQL Server in the ODBC connection).
It is a little old, but I found the following tutorial very helpful when I was learning this process: Installing and configuring Oracle Heterogeneous Services for SQLServer.
Once you have the database link working, on the Oracle side you could then create views/synonyms/materialized views (complete refresh only) over the SQL Server tables for easier access.
As for the other way around (how to view data in Oracle from SQL Server), i'm not sure since i'm exclusively an Oracle DBA...
