Introduction to ORACLE 8i common connectivity Technology

Source: Internet
Author: User
Tags copy count database join log connect odbc ole oracle database
Introduction to ORACLE 8i common connectivity Technology


Welcome everyone to exchange with me: small white

Welcome to reprint, please keep this statement, thank you!


The concept of common connectivity technology introduced in Oracle 8.1.6 (Generic connectivity), a connectivity solution that addresses data access requirements for many heterogeneous database storage without the need to install a transparent gateway for Oracle (Oracle Transparent Gateway). This feature allows transparent connections to be established using industry standard ODBC and OLE DB.

What is a normal connection (Generic connectivity)

The common connection is a low-end data integration solution for Oracle, with the goal of providing Oracle 8i the ability to connect to a non-Oracle database. It is implemented through heterogeneous service agents (heterogeneous services).

Types of heterogeneous Service agents:
ODBC Agent for accessing the ODBC data Providersole 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)
System structure diagram for common connections

The process of accessing a non-Oracle database by an Oracle client program: The client program connects the Oracle server via Sql*net, and the access to the heterogeneous database is delivered to the HS agent.

The HS agent is implemented through the following functional components: The ODBC Administrator->ODBC driver-> The network client of the database-> the target database.

If the heterogeneous library and the Oracle database are on the same server, the driver is generally able to communicate directly with the local database, which eliminates the network client layer of the database.

Conversion of data types

Oracle automates the conversion of ODBC and OLE DB data types to Oracle data types, and the specific corresponding transformations can refer to Oracle's online help /doc/server.817/a76960/datatype.htm.

Restrictions on normal connections

1 A table containing a BLOB column must have a primary key field;

2 Blob/clob data not directly through the pass-through query method;

3 Updates or deletes statements that include functions in the WHERE clause are not allowed;

4 does not support calls to stored procedures;

The HS agent (ODBC/OLEDB) does not support distributed transactions (distributed transactions) and supports only single point transactions (Single-site transactions).

Configuration of common connection agents

Let me introduce you to the ODBC configuration with normal connections. Use the HS ODBC proxy to connect to SQL SERVER 2000.

The first step  initialization file creation

First, you have to create the initialization file. ORACLE provides sample initialization files, the name init<agent>.ora,<agent> may be hsodbc,hsoledb,hsolefs, representing three types of proxies, located in $oracle_home/ Hs/admin under.

Copy the corresponding file and rename it to Init
Copy and create a Inithssql2k.ora file, edit the Inithssql2k.ora file. Parameters are:

# This is a sample agent init file, contains the HS parameters that are

# needed for an ODBC Agent.


# HS init parameters


#HS_FDS_CONNECT_INFO = <odbc data_source_name>

#HS_FDS_TRACE_LEVEL = <trace_level>

Hs_fds_connect_info = SQL2K

Hs_fds_trace_level = On

Hs_autoregister = TRUE


# Environment variables required for the non-oracle system


#set <envvar>=<value>

The second step is to create a data dictionary of heterogeneous Service agents

Perform $oracle_home/rdbms/admin/caths.sql to create related system tables and views.

Step three creating a working environment for heterogeneous service agents

Add a hssql2k listening service, $ORACLE _home/network/admin/listener.ora






Modify $oracle_home/network/admin/tnsnames.ora to add a network service name

SQL2K, which will be used when you create a database join later.



(Address= (PROTOCOL=TCP) (Host=localhost) (port=1521))

(Connect_data= (SID=HSSQL2K))



Reboot listening and view service status

Lsnrctl for 32-bit windows:version on 02-jul-2003 12:49:


(c) Copyright 1998 Oracle Corporation. All rights reserved.

Welcome to Lsnrctl, type ' help ' for information.

lsnrctl> status

Connecting to (description= address= (protocol=tcp) (Host=sam) (port=1521))




Version Tnslsnr for 32-bit windows:version


Start Date 02-jul-2003 11:08:31

Uptime 0 days 1 hr. 2 sec

Trace level off

Security off

SNMP off

Listener Parameter File D:\oracle\ora81\network\admin\listener.ora

Listener Log File D:\oracle\ora81\network\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 completed successfully


The HSSQL2K listening service is already functioning properly.

Step fourth Modify the database startup parameter file

Modify the database startup parameter file, set Global_names=false, and if set to True (default), the database connection name and global database name are the same, which can cause ORA-02085 errors.

Step fifth Create a connection to access the Non-oracle database (db link)

In Sqlplus, execute the CREATE DATABASE link command. Connect to specifies the username and password for connecting 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

With the partitioning option

Jserver Release

Sql> Connect Scott/tiger;


Sql> Select COUNT (*) from SYSOBJECTS@SQL2K;




Oracle now has access to the SQL Server database. All above, I refer to Oracle's online help, and the actual test pass. The author also found that the use of HSODBC is not very stable, and some SQL statements will cause HSODB agent fatal error, at the same time there are many restrictions, the use of transparent gateway should be able to achieve very good results.

However, this feature of Oracle 8i allows the installation of additional transparent gateway products. Therefore, as long as the application of data access requirements can be met, the adoption of generic connectivity is also a fast, economical method.

The author's test environment: Windows2000 server+oracle 8i (8.1.7)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.