Oracle Linked Server

Following are the steps involved in creating a linked server and working with stored procedures on the Oracle Database

1. Install the Oracle Client software on the server
2. Create a linked server as follows
EXEC sp_addlinkedserver
'OracleLinkedServer', 'Oracle',
'MSDAORA', 'OracleServer'

The name of the linked server is Oracle-LinkedServer.

The second parameter, product name (Oracle),is optional.The third parameter specifies the OLE DB provider.

The third parameter MSDAORA is the name of the Microsoft OLE DB Provider for Oracle.

Thr fourth parameter is the data source name of the Oracle Server (This is the TNS name of Oracle server which should work when pinged using a utility called TNSping and the servername this utility is installed with the client software of Oracle.)

3. Add Login information for the linked server
EXEC sp_addlinkedsrvlogin '
OracleLinkedServer ', false,
'SQLuser', 'OracleUser',
'OraclePwd'

The first parameter, Oracle Linked Server, specifies the name of the linked server system that you created.

The second parameter determines the name of the login to be used on the remote system.A value of True indicates that the current SQL Server login will be used to connect to the linked server. This requires that the logins on the two database servers match, which is typically not the case.A value of False means you'll supply the remote login.

The third parameter specifiesthe name of a SQL Server login that this remote login will map to.A value of NULL indicates that this remote login will be used for all connections to the linked Oracle server. If the Oracle system uses Windows authentication, you can use the keyword domain\ to specify a Windows login.

The fourth and fifth parameters supply login and password values for the Oracle system.


3. To be able to use stored procedures on Oracle the RPC paramter should be enabled on the server which is done as follows:-
sp_serveroption 'ORALINK', 'rpc out', 'true'
sp_serveroption 'ORALINK', 'rpc', 'true'


4. To access the tables on a linked server, use a four-part naming syntax: linked_server_name.catalog_ name.schema_name.table_name.

For example, to query the sample Oracle Scott database, you'd enter the statement

SELECT * FROM OracleLinkedServer..SCOTT.EMP
(Please mark the double dots after the server name which means that the catalogue or database name is not required and used by default this is because in oracle each database is treated as a server with a TNS name)


5. Test a stored procedure
declare @lstatus varchar(240)
set @lstatus = ''
execute ( 'BEGIN JNC_RMS_API.CHECK_CONNECTIVITY( ? ); END;', @lstatus output) at ORALINK
print @lstatus

Please ensure that the strings being sent for output parameters are initialized with a default value ( set @lstatus = '' ) else the procedures don't execute.