Oracle gateways Transparent gateway accesses SQL Server

With its native Oracle 12c installed, the company's platform needs to support both Oracle and SQL Server, often with the need to synchronize data from Oracle to SQL Server. The purpose can be achieved through the SQL Server link server, but because SQL Server is a 64-bit version, it has failed many times. Installing 32-bit SQL Server is also too lazy to toss.
Because Oracle is 12c, the gateways installed is also 12c. An attempt was made to install the 11g gateways on the Oracle database server (native), but the configuration failed.
Oracle Gateways 12c is also available on the Oracle Database 12c download page. Find the corresponding gateways for your database version.
Before installing, set the environment variable Oracle_home, mine is: C:\oracle\product\12.1.0\dbhome_1
When installing, select the database you want to access, and here is SQL Server. The gateways is installed in the same directory as the Oracle database software.
Installation process:

Configuration is the focus. The 10g configuration is found on the Internet. The 12c directory name is slightly different from 10g:
Listener.ora
Sid_list_listener =
(Sid_list =
(Sid_desc =
(Sid_name = Clrextproc)
(Oracle_home = C:\oracle\product\12.1.0\dbhome_1)
(program = Extproc)
(Envs = "Extproc_dlls=only:c:\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
)
(Sid_desc =
(sid_name = dg4msql)
(oracle_home = C:\oracle\product\12.1.0\dbhome_1)
(program = dg4msql)
    )
  )
LISTENER =
(Description_list =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521))
)
)
Add the following to Tnsnames.ora:
Dg4msql =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521))
(Connect_data =
(SID = dg4msql)
)
(HS = OK)
)
In Oracle_home/dg4msql/admin/initdg4msql.ora
Confirm that the following content is in the file Initdg4msql.ora ():
hs_fds_connect_info=server Name,port number//database name
Hs_fds_trace_level=off
Hs_fds_recovery_account=recover
Hs_fds_recovery_pwd=recover
Create a DB Link:
Create Public database link Ms_link
Connect to SA identified by password
Using ' Dg4msql ';
Restart monitoring.
Access the SQL Server table in the form of select * from [email protected]_link. You cannot insert data directly into the SQL Server table by using INSERT INTO [email protected]_link select * FROM table. The error hint is that both the source and destination tables should be in the same database.
My workaround is to use a piece of anonymous script one data at a time. I don't know if there's any better way.
At this point, you have successfully accessed SQL Server data in the Oracle database.

from:http://blog.itpub.net/8520577/viewspace-1066213/

Oracle gateways Transparent gateway accesses SQL Server