Oracle connects to the odbc data source, oracleodbc

Oracle connects to the odbc data source, oracleodbc

Connect Oracle to odbc Data Source

Description

There are two ways to connect oracle to the ODBC Data source: hsodbc and dg4odbc. In short, dg4odbc is an upgrade of hsodbc. The two connection methods are roughly the same. The connection steps are described as follows:

Check whether the DG4ODBC driver is installed

The method is as follows:

On the Oracle server, run the command (dg4odbc or hsodbc) in the cmd window)

Configure ODBC driver

The ODBC data source must be configured in the system DSN. Note that the ODBC data source must be consistent with the application architecture bits. Otherwise, the 64-bit version and 32-bit version do not match.

Default location of 64-bit ODBC:

C: \ Windows \ System32 \ odbcad32.exe

Default 32-bit ODBC location:

C: \ Windows \ SysWOW64 \ odbcad32.exe

In this example, the ODBC Data Source Name Is testodbc.

Configure dg4odbc (hsodbc)

In the "ORACLE_HOME \ hs \ admin" directory, the default name is "initdg4odbc. ora "/" inithsodbc. ora file, copy "initdg4odbc. ora "/" inithsodbc. ora "file, new file name changed to" initodbc_test.ora ",

[Each instance that uses DG4ODBC must have a separate "init *. ora" file named after init + <gateway sid> +. ora]

Parameter description

HS_FDS_CONNECT_INFO = DSN name in ODBC Data Source Management [this experiment is testodbc]

HS_FDS_TRACE_LEVEL = OFF [tracing level parameter, which is not configured or configured as "OFF" due to performance impact. If you need to track logs in case of gateway problems, set it to "Debug ", trace log files are stored in the "ORACLE_HOME \ hs \ trace" directory]

Configure gateway listening

You can configure the following parameters for a gateway listener:

SID_NAME: SID of the Gateway

ORACLE_HOME: Directory

PROGRAM: Specifies the executable PROGRAM that the listener service responds to an ODBC connection request.

Reference Configuration:

# Listener. ora Network Configuration File: C: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \ network \ admin \ listener. ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = CLRExtProc)

(ORACLE_HOME = C: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1)

(PROGRAM = extproc)

(ENVS = "EXTPROC_DLLS = ONLY: C: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \ bin \ lrlr11.dll ")

)

(SID_DESC =

(SID_NAME = odbc_test)

(ORACLE_HOME = C: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1)

(PROGRAM = dg4odbc (or hsodbc ))

)

)

Restart the listening service

Lsnrctl stop

Lsnrctl start

Configure TNS

Open the "ORACLE_HOME \ network \ admin \ tnsnames. ora" File

The parameters to be configured are as follows:

Connect_descriptor: Enter the custom TNS connection name.

ADDRESS: Enter the ip address and port of the transparent gateway.

SID: SID of the gateway.

HS: Specifies the non-ORACLE database to be connected.

Odbc_test =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)

(HOST = 127.0.0.1)

(PORT = 1521)

)

(CONNECT_DATA =

(SID = odbc_test ))

(HS = OK ))

Configure DBLINK

Connect to OracleDB using sqlplus, PLSQL Developer, or TOAD, and execute the following DDL statement to create DBLink

In the "create database link" statement, we recommend that you enclose the username and password in double quotation marks to avoid auto Oracle conversions in Case sensitivity. Generally, you can use an excel Data source instead of a password. SQL server requires a password.

Create database link odbc_test

Connect to "2222"

Identified by "2222" using 'odbc_test ';

Complete