Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
In SQL Server I can create a view that is a join between two tables that are in completely different servers using Linked Server. If I change one of the servers from SQL Server to be Oracle, can I still do the same?
I need the join table to be in Oracle
Yes, you can.
In Oracle, this is called "Heterogeneous Connectivity" This Oracle admin manual has details.
This article walks through an example case of doing the above steps with details.
The basic outline is:
- Install ODBC drivers on Oracle server
- Configure ODBC to talk to the SQL Server DB (create a system DSN)
- Test the ODBC
- Configure Global_Names parameter to false.
- Create initodbc.ora file to configure Heterogeneous services
- Modify listener.ora to connect to both Oracle and ODBC drivers
- Modify tnsNames.ora file
- Reload the listener to see the above changes.
- Create a DB Link from Oracle to the ODBC connection.
- Test via SQL Select statement.
Here is how I created a database link so that I could connect to SQL Server from my Oracle database.
My existing environment:
%ORACLE_HOME%\BIN\dg4odbc.exe exists, Database Gateway is probably installed already.) Oracle instance is operational and fully functional in our organization, so important listener.ora and tnsnames.ora files are configured correctly. I ran Select * from v$parameter where name = 'global_names' and confirmed that the value was false.On Windows server hosting Oracle database, I opened up ODBC (64-bit) and created a System DSN pointing to my Microsoft SQL Server. I tested it and connectivity was successful. (TIP: make sure to set the database you want to connect to as the default database in this DSN connection, as it may will be the only one you can access from your Oracle link, if you have multiple databases.)
I created a file called init?????.ora in the directory %ORACLE_HOME%\hs\admin\, where ????? is the name of the System DSN I created in Step 1. This file contains the following lines:
HS_FDS_CONNECT_INFO=<System DSN name>
HS_FDS_TRACE_LEVEL=off
Edited my listener.ora file located at %ORACLE_HOME%\NETWORK\ADMIN\. There were already a bunch of SID_DESC items nested within (SID_LIST_LISTENER = (SID_LIST = ... We need to add one (properly nested) for our ODBC System DSN:
(SID_DESC =
(PROGRAM = dg4odbc)
(ORACLE_HOME = <path to your ORACLE_HOME>)
(SID_NAME = <System DSN name>)
)
Created an entry in my tnsnames.ora file (located in same directory as listener.ora):
<Whatever name we give this connection>=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=localhost)
(PORT=1521)
)
(CONNECT_DATA=(SID=<System DSN name>))
(HS=OK)
)
In SQL*Plus or SQL Developer, I created a database link. This is where I entered the credentials needed to connect to the target SQL Server:
CREATE PUBLIC DATABASE LINK <Name of DB Link>
CONNECT TO "user" IDENTIFIED BY "password"
USING '<Name we gave the connection in tnsnames.ora>';
Opened an elevated command prompt and restarted the Oracle listener service by running lsnrctl stop and then lsnrctl start (shouldn't affect connected users...)
Opened up SQL Developer and tested it out by running a query against the now linked SQL Server database (as limited by the access of the account used to access it).
--Basic ODBC connectivity test:
select * from dual@<Name we gave link in Step 5>
SELECT * FROM SomeTable@<Name we gave link in Step 5>;
SELECT * FROM <optional schema.>SomeTable@<Name we gave link in Step 5>;
Proper Oracle documentation can be found here.
