CODEWITHSUNDEEP
sql-serveroracle-databaset-sqlstored-procedures
Nickz
Nickz

Reputation: 1880

Accessing Oracle database from SQL Server stored procedure.

I was wondering if someone could give me some pointers on how to connect to an Oracle Database from stored procedure in SQL Server.

Upvotes: 0

Views: 2559

Answers (1)

Pasha Immortals
Pasha Immortals

Reputation: 839

Firstly on your SQL Server server you will need to install Oracle Client Tools, the reason behind this is any machine that wants access to a Oracle Database will need the necessary Oracle Client Tools installed to access it.

Secondly you will need to configure the file "tnsnames.ora" that is installed with the above configured to point to the required Oracle Server/Database that you want to connect to.

Now you will need to create a Linked Server from your SQL Server management studio for that server to the oracle server.

Then in your stored procedure you have to follow the rule of: [SERVER_NAME].[DATABASE].[OWNER].[OBJECT] in order of accessing thing on the oracle database, but keep in mind this is SQL Server syntax and there might be some minor twiking needed.

This is a rough outline and I hope it has set you on your way.

Upvotes: 2

Related Questions