Link Server: SQL to Oracle

Get details of oracle like user credentials, global database name, etc:

Oracle Information:

Following are my details:

Username and password: 

user: hr
pass: hr  

To get global oracle database name:

Global db name: orcl.CTPL.in 

System identifier: orcl 

Pass: sys 

Installing ODAC Driver:

Note:- Name orcl of oracle -- Configure ORAOLEDB.Oracle provider so that runs in process within SQL Server

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1

Extract downloaded file and install

Creating Link Server Connection:

Tsql script:

USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'ORACLEDB', @srvproduct=N'oracledb', @ provider=N'SQLOLEDB', @datasrc=N'orcl'

TSQL Script:Database schema

USE [master] 

GO EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'ORACLEDB', 
@locallogin = NULL , @useself = N'False', 
@rmtuser = N'hr', 
@rmtpassword = N'hr' 

GO

Rpc connection:

EXEC master.dbo.sp_serveroption @server=N'ORACLEDB', 
@optname=N'rpc out', @optvalue= N'true' GO

Query the table:

CRUD Operations: 

Make table into oracle database and insert some values

1. Select

2. Deleting from tables

If constarints are there then dependencies should removed

3. Insert

4. Update

Update Succeed, lets query:

5. Procedure

Set rpc out to true in link server: