Introduction to the ordinary connection technology of Oracle 8i
=====================================
Welcome everyone to communicate with me: Xiaobai enhydra_boy@tom.com
Welcome to reprint, please keep this statement, thank you!
=====================================
Generic Connectivity in Oracle 8.1.6, which satisfies data access requirements for many heterogeneous data reservoirs, and does not need to install Oracle Transparent Gateway. This feature allows the use of industry standard ODBC and OLEDB to establish transparent connections.
What is generic connection (Generic Connectivity)
Ordinary connection is a low-end data integration solution for Oracle, and the goal is to provide Oracle 8i's ability to connect to non-Oracle databases. Its implementation is achieved by Heterogeneous Services.
Type of Heterogeneous Services:
ODBC Agent for Accessing ODBC Data Providers
OLE DB Agent for Accessing Ole DB Data Providers That Support SQL Processing - Sometimes Referred To As Ole DB (SQL)
ODBC Agent for Acceng Ole DB Data Providers WITHOUT SQL Processing Support - Sometimes Referred To As Ole DB (FS)
Ordinary connection system structure diagram
Oracle client program access to non-Oracle database: The client program is connected to the Oracle server through SQL * NET, and is handed over to the HS proxy implementation.
The HS agent implements: ODBC Manager -> ODBC Driver -> Database of the Database.
If the heterogeneous library and the Oracle database are on the same server, it is generally that the driver can communicate directly with the local database, which can save this layer of the network client of the database. Data type conversion
Oracle automatically implements converted ODBC and OLEDB data types to Oracle data types, specific corresponding conversions can refer to Oracle's online help http://download-west.Oracle.com/docs/cd/a87860_01/doc/server.817/a76960 /Datatype.htm.
General connection limit
1 Table containing a BLOB column must have a primary key field;
2 BLOB / CLOB data does not directly pass the PASS-THROUGH query method;
3 Updates or deletes statements including functions in the WHERE clause are not allowed;
4 Do not support the calling process;
The HS Agent (ODBC / OLEDB) does not support distributed transactions, only supports single-site transactions (SINGLE-Site Transactions). Under the Configuration of Ordinary Connection Agent, I will introduce it to use the ODBC configuration of ordinary connections. Connect SQL Server 2000 with HS ODBC proxy.
The first step Initialization file is established
First, you have to create an initialization file. Oracle provides a sample initialization file, the name is init
Copy the corresponding file and change the name INIT
Copy and create an ITHSSQL2K.ORA file, edit the Inithssql2k.ora file. Parameters are:
# This is a sample agent init file what contains the HS Parameters That Are
#needed for an odbc agent.
#
# HS init parameters
#
#HS_FDS_CONNECT_INFO =
#HS_FDS_TRACE_LEVEL =
HS_FDS_CONNECT_INFO = SQL2K
HS_FDS_TRACE_LEVEL = ON
HS_AUTOREGISTER = TRUE
#
# Environment Variables Required for the Non-Oracle System
#
#set
Step 2 Creating a DBD Dictionary
Perform $ ORACLE_HOME / RDBMS / Admin / Caths.sql, create related system tables and views.
The third step to create a working environment of a heterogeneous service agent
Add HSSQL2K listening service, $ oracle_home / network / admin / listener.ora
SID_DESC =
(SID_NAME = HSSQL2K)
(Oracle_Home = D: / Oracle / ORA81)
(Program = HSODBC)
)
Modify $ ORACLE_HOME / Network / Admin / TNSNames.ora, add a network service name SQL2K, which will be used later when you create a database connection.
SQL2K =
(Description =
(Address = (protocol = tcp) (Host = localhost) (port = 1521)))
(Connect_Data = (SID = HSSQL2K))
(HS = OK)
)
Restart listening and look at service status
LSNRCTL for 32-Bit Windows: Version 8.1.7.0.0 - Production On 02-JUL-2003 12:49:
30
(c) CopyRight 1998 Oracle Corporation. All Rights Reserved.
Welcome to lsnrctl, Type "Help" for Information.
LSNRCTL> Status
Connecting to (deSCription = (pecol = tcp) (host = sam)) status of the listener
---------------------------------------------------------------------------------------------------------------------------------------
Alias Listener
Version TNSLSNR for 32-Bit Windows: Version 8.1.7.0.0 - Product
Tion
Start Date 02-Jul-2003 11:08:31
Uptime 0 days 1 hr. 41 min. 2 sec
Trace Level Off
Security off
SNMP OFF
Listener parameter file d: /oracle/ora81/neetwork/admin/listener.ora
Listener log file d: /oracle/ora81/neetwork/log/listener.log
Services Summary ...
Orcl Has 1 Service Handler (S)
Orcl Has 3 Service Handler (s)
PLSextProc HAS 1 Service Handler (s)
HSSQL2K HAS 1 Service Handler (s)
THE Command Complated SuccessFully
Lsnrctl>
The HSSQL2K listening service has been running normally.
Step 4 Modify the database startup parameter file
Modify the database startup parameter file, set global_names = false, if set to true (default), the database connection name is the same as the global database name, which will cause an error in ORA-02085.
Step 5 Create a connection to the Non-Oracle database (Database Link)
In SQLPLUS, execute the CREATE DATABASE LINK command. Connect To Specifies the username and password connected to SQL Server.
SQL> CREATE PUBLIC DATABASE LINK SQL2K Connect to Sa Identified by Manager Using 'SQL2K';
Database link created.
Sixth step test
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
WITH THE PartInog Option
Jserver Release 8.1.7.0.0 - Production
SQL> Connect Scott / Tiger;
Connected.
SQL> SELECT Count (*) from sysobjects @ SQL2K;
Count (*)
------------
135
Now, Oracle has access to the SQLServer database. These are all, the author refers to Oracle's online help, and the actual test has passed. The author also found that HSODBC is not very stable, some SQL statements will cause a fatal error of HSODB Agent, and there are many restrictions, and the transparent gateway should get a good effect.
However, this feature of Oracle 8i can not install additional Transparent Gateway products. Therefore, as long as it can meet the application's data access requirements, generic connectivity is not lost as a fast and economical approach. Item: Windows2000 Server Oracle 8i (8.1.7)
