We are trying to, via PeopleSoft running on SQL Server, extract rows from an Oracle database using an SQR. The production server, where PeopleSoft and SQL Server exists, does not and will never have an Oracle environment installed. Is there a way to install an Oracle ODBC driver without having Oracle installed or, can I create a database link between SQL Server and Oracle?
I think you will need the Oracle Client software installed (NOT the Oracle Database NOTE) as this would be needed by whatever ODBC/OLEDB driver you have (unless someones created the equivalent of the JDBC thin client driver which doesnt need the Oracle SQLNET libraries).
The other possiblities are to have the
1)Oracle Database push data to the SQLServer database using the Oracle Gateway products.
2) An intermediate SQLServer database which does have the Oracle Client installed and have the production database send queries to this intermediate database which then passes it onto Oracle. But this will cost more and much worse performance.
3) Write some sort of java app which uses the Oracle thin client JDBC driver to talk to Oracle. Only problem is how to get SQLServer to talk to the java app.