Do you want to use Oracle’s driver to directly connect to an Oracle database instead of using ODBC (DSN)? Is it any faster? You be the judge!
Step 1
Make sure you have Oracle 12g drivers installed on the server. This could be any version of Oracle
Step 2
Make a note as to where your Oracle installation path is. You’ll need a few information from the TNSADMIN.ora file. For example, my path is C:\Oracle\Ora12\Network\Admin\tnsadmin.ora
Inside tnsadmin.ora, you’ll need the HOST, PORT, and SERVICE_NAME
Step 3
In SSMS, navigate to the OraOLEDB.Oracle providers and open up the properties
Step 4
Create a Linked Server
- Linked Server: Call this Linked Server whatever you want
- Provider: Select Oracle Provider for OLEDB
- Product name: Call this whatever you want
- Data source: This information comes from Step 2 in this format HOST:PORT/SERVICE_NAME i.e.(My_Host.afterowl.com:1571/My_Service_Name.afterowl.com)
- Click on the Security tab on the left and use Be made using the security context. Then click OK to create the Linked Server
Step 5
This is how you would use your new shiny Oracle Linked Server
SELECT * FROM OPENQUERY(“ORACLE”, ‘SELECT * FROM MyTable’) AS MyTable
afterowl 
