Setting up a Linked Server to an Oracle database

To create a linked server you can use the following T-SQL:

USE MASTER
GO

EXEC master.dbo.sp_MSset_oledb_prop 'OraOLEDB.Oracle', 'AllowInProcess', 1
GO
EXEC sp_addlinkedserver
@server = 'ORA_DB',
@srvproduct = 'Oracle',
@provider = 'OraOLEDB.Oracle',
@datasrc = 'ORA_DB'
GO
Exec sp_serveroption 'ORA_DB' , 'rpc' , TRUE
GO
Exec sp_serveroption 'ORA_DB' , 'data access' , TRUE
GO
Exec sp_serveroption 'ORA_DB' , 'rpc out' , TRUE
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = 'ORA_DB',
@useself = 'False',
@rmtuser = 'madvip',
@rmtpassword = 'password'
GO

GO

You can then query the Oracle database through the linked server using the following command:

SELECT * FROM linked-server..SCHEMA.TABLE;