Author: kamusdate: 2003-12
First, set the Gateway machine (Gateway can be on the same machine with Oracle Database, or separate) 1. Install transparent gateway
2. Set Oracle_Home / TG4MSQL / Admin / INITG4MSQL.ORA Modification: HS_FDS_CONNECT_INFO = "Server = Beibei; Database = Northwind"
3. Set ORACLE_HOME / network / admin / listener.ora change: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = C: / oracle / ora90) (PROGRAM = extproc)) (SID_DESC = (SID_NAME = tg4msql) (Oracle_Home = C: / Oracle / ORA90) (Program = TG4MSQL)) (SID_DESC = (Global_DBNAME = ORADB.SERAPHIM) (Oracle_Home = C: / Oracle / ORA90) (SID_NAME = ORADB)))))
Second, set the Oracle Database Machine 4. Set Oracle_home / Network / Admin / TNSNames.ora (Oracle Server Require This configuration Use Gateway) Add: MSQL = (deSCription = (address = (address = (protocol = tcp) (port = seraphim)))))) (port = 1521)))) Connect_data = (SID = TG4MSQL)) (HS = OK)) Host: refers to the machine where Gateway is located. Port: SID specified in the Listener.ora file in the Gateway machine: must be the same HS = OK in the SID_NAME specified in the listner.ora file in the Gateway machine: Specify that the connection will use Oracle's heterogeneous service
5. Check ORACLE_HOME / network / admin / sqlnet.ora If the file contains: names.directory_path = (TNSNAMES, HOSTNAME) names.default_domain = worldname.default_zone = world these lines, it must be added in 4 connect_descriptor .world suffix (in the example above MSQL must be msql.world)
6. Modify the init.ora file Setting global_names = false, restart the database (if the original setting is global_names = true). Otherwise, the SQL Times Wrong: ORA-02085: Database Link MSQL and HO.World Conception Reason: The global_names parameter When Set to True Implies That Database Link Name Should Be
Similar to the global database name to which you are trying to connect.7. Create Database LinkCreate Public Database Link Msql Connect Tohr Identified by HR Using 'MSQL'
Third, set the SQLServer server 8. Create user HR / HR and give the corresponding database (this example uses database northwind) retrieval authority or write permission.
9. You can now retrieve data in SQLServer by SQL. SELECT Count (*) from all_tables @ msql;
SELECT "Orderid", "ShipName" from "Orders" @MSQL
select orders. "OrderID", orderdet. "Quantity", products. "ProductName" from "Orders" @msql orders, "Order Details" @msql orderdet, "Products" @msql productswhere orders. "OrderID" = orderdet. "OrderID "and orderdet." productid "= products." ProductID "
Note that you need to add double quotes to the table name and field name to be read.
Recommendation: In order to easily retrieve the data of SQL Server in Oracle, create views (CREATE VIEW) on the table that needs to be retrieved.
This is just like operating the local Oracle table when searching.
