Access oracle table from sql server

Access oracle table from sql server

First and Foremost you need to create on DSN (Here OracleSvr)   to connect to Oracle from SQL server (Microsoft)

After creating the DSN and testing it the following commands can be used to select the data from the Oracle database

Here's the SELECT syntax: ( To be run in SQL Server )

SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')

Here's the UPDATE, INSERT, DELETE syntax:

UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')

SET name = 'ADifferentName';

INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')

VALUES ('NewTitle');

DELETE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name =

''NewTitle''');