We have source data in both Oracle and SQL Server. Our target is to create a staging database in Oracle and bring source data from both databases. Right now we have not planned for using any ETL tools for this extraction but Oracle PL/SQL. Is it possible to extract from the SQL Server also?
From within Oracle, you can use Transparent Gateways to access data in SQL Server through a database link. Oracle will think it is querying another Oracle database, but the data is coming from SQL Server. Please see the following for more information.
Oracle Transparent Gateway for Microsoft SQL Server Administrator's Guide
Another alternative is to use SQL Server's Data Transformation Services (DTS) to push data from SQL Server into an Oracle db via ODBC.