It is required sometime to connect oracle database to SQL Server database using uni-direction or bi-direction methods. Since these are two different database so something as simple as direct DBLINK will not work here. Below are the basic concepts of making connection from SQL SERVER to Oracle and vice-versa.
1.CONNECTING FROM SQL SERVER TO ORACLE USING LINKED SERVERS
PURPOSE/FUNCTION:
Use this solution to Read data of Oracle database table from MSQL Server Database.
If you need to retrieve and use Oracle data in your SQL Server applications, then SQL Server’s ‘linked servers concept’ can be used. SQL Server’s linked servers functionality lets you seamlessly integrate your SQL Server applications with tables, indexes, and views stored in Oracle.
PRE-REQS
- Need to install the Oracle Data Access Components (ODAC) on the SQL Server 2008 system
The Oracle Data Access Components (ODAC) software provides the network libraries that are required to establish connectivity to the Oracle database.
HIGH LEVEL STEPS
- Install Oracle Data Access Components (ODAC) software
- Configure connection to Oracle server by modifying Oracle’s tnsnames.ora file
- Configure the Linked Server by using either SQL Server Management Studio (SSMS) or by executing T-SQL statements
- Execute Queries with proper syntax
Queries that are directed to linked servers use the following four-part naming syntax: linked_server_name.catalog(database)_name.schema_name.table_name. For example, to select all of the rows from the EMP table you would use the following SQL statement:
SELECT * FROM \[OR-PORT-TESTORA11G\]..SCOTT.EMP.
OR-PORT-TESTORA11g is the linked server name. When using the Oracle OLE DB provider, the catalog name should be left blank. The schema name is SCOTT. This corresponds to what SQL Server users would consider the database name. The table name is EMP.
REFERENCE
Microsoft Article: How to get up and running with Oracle and Linked Servers
2. CONNECTING FROM ORACLE TO SQL SERVER WITH DATABASE GATEWAY FOR SQL SERVER (DG4MSQL)
PURPOSE/FUNCTION
Use this solution to Read data of Microsoft SQL server table from Oracle database.
Note that unlike setting up a connection to Oracle Database from SQL Server which is fairly easy, this solution is not so straight forward and requires installing another Oracle software component which acts as middleman.
Starting with Oracle database 11g, Oracle now provides a Database Gateway for MS SQL Server and various other databases.
PRE-REQS
- Need to install and configure the Oracle Database Gateway for Microsoft SQL Server (DG4MSQL) software on the Oracle side
HIGH LEVEL STEPS
- Download and Install Oracle Database Gateway for Microsoft SQL Server
- Configure Database Gateway for Microsoft SQL Server (DG4MSQL)
— Configuring requires modifying configuration files with MSQL server details, starting anew listener process on the Oracle side and creating a database link.
- Execute Queries using DBLINK
Example:
SQL> select count(1) from “SQLView”@dblink_to_sqlserver;
Here SQLVIEW is the table on the MSQL server side and dblink_to_sqlserver is the dblink created in Oracle server pointing towards MSQL Server database.
Reference:
- Master Note for Oracle Gateway Products (Doc ID 1083703.1)
- How to Setup DG4MSQL (Database Gateway for MS SQL Server) on Windows 32bit (Doc ID 466267.1)
- Oracle Database Gateway Configuration Utility (Doc ID 1351616.1)
- How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) Release 11.1 or 11.2 on Linux x86 32bit – Post Install (Doc ID 437374.1)
- How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install (Doc ID 562509.1)
- Database Gateway and Generic Connectivity (DG4ODBC) Licensing Considerations (Doc ID 232482.1)
- Installation Overview For Oracle Database Gateway Products (Doc ID 1351618.1)
- Database Gateway information page: http://www.oracle.com/technetwork/database/gateways/index.html
IMPORTANT NOTE: There is another way to connect to MSQL Server from Oracle which is called “Connecting Oracle to SQL Server with Database Gateway for ODBC (DG4ODBC)” but that solution has some limitations as compared to “Connecting Oracle to SQL Server with Database Gateway for SQL Server (DG4MSQL)”.
Please read below Oracle Note Id to understand those limitations:
Functional Differences between Database Gateway for ODBC (DG4ODBC) and Specific Database Gateways (DG4MSQL, DG4Sybase, DG4Informix, DG4Teradata) (Doc ID 252364.1)
No comments:
Post a Comment