Setting Method for enabling Oracle to Access Multiple SQL servers at the same time

In Oracle, how does one set access to multiple SQL Server databases? Suppose we want to access the default pubs and Northwind databases in SQL Server at the same time in Oracle.

1. on a windows machine with Oracle9i Standard Edition or Oracle9i Enterprise Edition installed (Server IP: 192.168.0.2), select the Transparent Gateway (Oracle Transparent Gateway) to access the Microsoft SQL Server database: ORACLE9I_HOME \ tg4msql \ admin under the new write initpubs. ora and initnorthwind. ora configuration file.

The content of initpubs. ora is as follows:
HS_FDS_CONNECT_INFO = "SERVER = SQLSERVER_HOSTNMAE; DATABASE = pubs"
HS_DB_NAME = pubs
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_RECOVERY_ACCOUNT = RECOVER
HS_FDS_RECOVERY_PWD = RECOVER

The content of initnorthwind. ora is as follows:
HS_FDS_CONNECT_INFO = "SERVER = sqlserver_hostname; DATABASE = Northwind"
HS_DB_NAME = Northwind
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_RECOVERY_ACCOUNT = RECOVER
HS_FDS_RECOVERY_PWD = RECOVER

The contents of listener. ora under $ ORACLE9I_HOME \ network \ admin are as follows:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.2) (PORT = 1521 ))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test9)
(ORACLE_HOME = d: \ oracle \ ora92)
(SID_NAME = test9)
)
(SID_DESC =
(SID_NAME = pubs)
(ORACLE_HOME = d: \ Oracle \ Ora92)
(PROGRAM = tg4msql)
)
(SID_DESC =
(SID_NAME = northwind)
(ORACLE_HOME = d: \ Oracle \ Ora92)
(PROGRAM = tg4msql)
)
)

Restart the TNSListener service on the gateway-enabled Windows machine (IP: 192.168.0.2) (when you add an accessible SQL Server database following this step, the TNSListener service must be restarted ).

2. Configure tnsnames. ora on the server of Oracle8i and Oracle9i and add the following content:
Pubs =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.2) (PORT = 1521 ))
)
(CONNECT_DATA =
(SID = pubs)
)
(HS = pubs)
)
Northwind =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.2) (PORT = 1521 ))
)
(CONNECT_DATA =
(SID = northwind)
)
(HS = northwind ))

After saving tnsnames. ora, run the following command:
Tnsping pubs
Tnsping northwind
A similar prompt is displayed, that is, success:

Attempting to contact (DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.2)
(PORT = 1521) (CONNECT_DATA = (SID = pubs) (HS = pubs ))
OK (20 ms)
Attempting to contact (DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.2)
(PORT = 1521 )))
(CONNECT_DATA = (SID = northwind) (HS = northwind) OK (20 ms)

Set the database parameter global_names = false. Do not set the connection of the database to be consistent with the global name of the target database. Global_names = true is required, which is inconvenient.

Both oracle9i and oracle8i can use the SQL command to change the global_names parameter alter system set global_names = false under the DBA user to create a public database link:

Create public database link pubs
Connect to testuser identified by testuser_pwd using 'pubs ';
Create public database link northwind
Connect to testuser identified by testuser_pwd using 'northwind ';

(Assume that pubs and northwind under SQL Server have sufficient permissions to log on to testuser and the password is testuser_pwd)

Access data in the database of SQL Server:

Select * from stores @ pubs;
... Select *
From region @ northwind;
............

3. Notes for use:
When ORACLE accesses the database link of SQL Server, the field name is caused by double quotation marks when select * is used.
For example:
Create table stores as select * from stores @ pubs;
Select zip from stores;
ERROR is located in row 1st:
ORA-00904: Invalid column name
Select "zip" from stores;
Zip
-----
98056
92789
96745
98014
90019
89076

If you have selected six rows, use SQL Navigator or Toad to view the table creation statement from SQL Server to ORACLE:
Create table stores
("Stor_id" CHAR (4) not null,
"Stor_name" VARCHAR2 (40 ),
"Stor_address" VARCHAR2 (40 ),
"City" VARCHAR2 (20 ),
"State" CHAR (2 ),
"Zip" CHAR (5 ))
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
Initialization 131072
NEXT 1, 131072
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
)

Summary:
In Windows, the Oracle9i gateway server is in initsqlserver_databaseid.ora under the $ Oracle9i_HOME \ tg4msql \ admin directory.
In Windows, the Oracle9i gateway server listener. ora contains:
(SID_DESC =
(SID_NAME = sqlserver_databaseid)
(ORACLE_HOME = d: \ Oracle \ Ora92)
(PROGRAM = tg4msql)
)

In UNIX or WINDOWS, the ORACLE8I, ORACLE9I server tnsnames. ora:
Northwind =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.2) (PORT = 1521 ))
)
(CONNECT_DATA =
(SID = sqlserver_databaseid)
)
(HS = sqlserver_databaseid)
)
The sqlserver_databaseid must be consistent.