ORACLE and SQL SERVER INTEGRATION

Clarifications

I am a little unclear about your question. Are you trying to synchronise the data between the two databases or migrate data from one to the other? Do you have an application that pulls some data from one and some from the other?

A SQL Server master gets to Oracle easily using linked servers:
Using the Oracle OLEDB provider results in an error when a query is executed unless ?Allow InProcess? is checked in the ?Oracle Provider for OLEDB? configuration setup from ?Provider Options?? button.

Oracle query syntax does not work for a linked server because SQL Server resolves the query. E.g. SQL Server has no idea what ROWNUM means in the following query:

SELECT PRV_NUM FROM ORCL..JOHN.MYTABLE WHERE ROWNUM < 21;

This query works fine because the query text is passed through to Oracle as is:

Note: Use of a variable in place of query string is NOT allowed(?!) (really sucks if you need to modify the query)

SELECT COL1 FROM OPENQUERY(ORCL,
'select NVL(COL1, ' ') as COL1 from JOHN.MYTABLE WHERE ROWNUM < 21')

******************
If Oracle is the master, getting to SQL Server using Oracle transparent gateway on Linux\Unix is not possible. What I've done is to load the SQL Server JDBC driver into Oracle and use Java stored procedures and/or functions. Definitely more work, but does the trick.

if you want to go from oracle (on unix) to mssql, I've had some success with heterogenous services (the free bit of it, not the separately licensed stuff) and the following opensource odbc drivers
FreeTDS from http://www.freetds.org/
unix odbc from: http://www.unixodbc.org/

It's some work to set up initially, but once done, you can create a dblink in oracle to a sql server then use it in the normal way.

heres an example config I found
http://nextre.it/oracledocs/sqlserver_hs.html