Oracle Heterogeneous Services (Accessing ODBC datasource)


Heterogeneous services within oracle database allows to connection with non-oracle database systems. Oracle provides two types of Heterogeneous Services agents, Oracle Transparent Gateways and Generic Connectivity. This blog is based on Generic Connectivity which uses Generic Connectivity agents for ODBC and OLE DB that enable you to use ODBE and OLEDB drivers to access non-Oracle systems that have an ODBC or an OLE DB interface. Any data source compatible with the ODBC or OLE DB standards can be accessed using a Generic Connectivity agent.

This blog deals with connecting MySQL database from oracle instance. For this purpose, following are the configurations of host and version of database used in this blog.

# Centos (Oracle Server Running)
    * CentOS release 4.4 (Final)
    * Oracle RDBMS 11.1.0.6.0
    * Host name = oracledb
    * Database Name = orcl

# Centos (MySQL Server Running)
    * CentOS release 4.4 (Final)
    * MySQL DBMS "mysql Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i686) using readline 4.3"
    * Host name = mysqldb * Database Name = oraclecall


Step 1 : Installing Pakages

I installed unixOBCD and mysql-connector in my oracledb server. You can find those packages at http://www.unixodbc.org/ and http://www.mysql.com. It depends upon you and your system to use either compile the source or use rpm packages. This blog uses rpm packages. The following are versions of unixODBC and mysql-connector that I used.

Packages ODBC:
	unixODBC-2.2.11-1.RHEL4.1
Packages MySQL ODBC:
	mysql-connector-odbc-3.51.23-0.i386.rpm


Step 2: Preparing MySQL Database

After adding unixODBC and mysql-connector, prepare MySQL database to connect for. Point out the database and user to connect.

mysql> CREATE DATABASE oraclecall;
mysql> GRANT all on oraclecall.* to oracle@oracledb identified BY '123';
mysql> flush PRIVILEGES;

The above MySQL commands will create database named oraclecall, creates user oracle with password 123 and grant all privilege to user oracle on oraclecall database. flush privileges tells the server to reload the grant tables.


Step 3: Configuring odbc.ini

The followings are the odbc.ini configurations that should point out to driver MySQL odbc library. It should contain ODBC Data Source name and Data Source Specification for source database.

$ vi /etc/odbc.ini

[ODBC Data Sources]
 
myodbc3     = MySQL ODBC 3.51.23 Driver DSN
 
[myodbc3]

DRIVER       = /usr/lib/libmyodbc3.so
DESCRIPTION  = MySQL ODBC 3.51.23 Driver DSN
SERVER       = mysqldb
PORT         = 3306
USER         = oracle
PASSWORD     = 123
DATABASE     = oraclecall
OPTION       = 3
SOCKET       =
  • ODBC Data Sources: This section lists the DSNs and associates them with the name of the driver. You need to provide this section only if you use an ODBC driver manager from a third-party vendor.
  • Data Source Specification: Each DSN listed in the ODBC Data Sources section has a Data-Source Specification section that describes the DSN.
  • Driver: Path for the driver. Set this value to the complete pathname for the driver library.
  • SERVER: Database server on which database_name resides
  • PORT: Database server port on with database server listens
  • USER: User connecting to database server
  • PASSWORD: Password for user connecting to database server
  • DATABASE: Name of the database to connect.


Step 4: Testing odbc connection

The above configured odbc connection can be testing using isql (Interactive Structured Query Language) utility.

$ isql myodbc3 oracle 123 -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| HELP [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
SQL> CREATE TABLE tester(id INT, name VARCHAR(24));


Step 5: Prepare Oracle

Configure listener.ora

TAdd some extra parameters to the existing listener.ora file. to support HS service

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/app/oracle/product/11.1.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /oracle/app/oracle/product/11.1.0/db_1)
      (SID_NAME = orcl)
    )
    
    (SID_DESC =
      (PROGRAM = dg4odbc)
      (ORACLE_HOME = /oracle/app/oracle/product/11.1.0/db_1)
      (SID_NAME = myodbc3)
      (ENVS=LD_LIBRARY_PATH=/usr/lib:/oracle/app/oracle/product/11.1.0/db_1/lib)
    )
    
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracledb)(PORT = 1521))
    )
  )

Here in bolded text within SID_DESC specifies:

  • SID_NAME: specifies the SID of the gateway and matches the gateway SID specified in the connect descriptor entry in the tnsnames.ora file.
  • ORACLE_HOME: specifies the Oracle home directory where the gateway resides.
  • ENVS: specifies the ODBC driver library path
  • PROGRAM: specifies the executable name of the Oracle Database Gateway for ODBC.

Note: HS Generic Connectivity using ODBC (HSODBC) has had it's name changed to Oracle Database Gateway for ODBC (DG4ODBC).

Now reload the listener utility to load the changed configuration to listener.ora file.

$ lsnrctl stop
$ lsnrctl start 
$ lsnrctl status 

Configure tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLEDB)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )
 
dg4odbc =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORCLEDB)(PORT = 1521))
    )
    (CONNECT_DATA = (SID= myodbc3))
    (HS=OK)
  )
  

Here in line of text in bold specifies:

  • dg4odbc: is the description of the object to connect to as specified when creating the database link.
  • TCP: is the TCP protocol used for TCP/IP connections.
  • HOST specifies the machine where the gateway is running.
  • PORT matches the port number used by the Oracle Net Listener that is listening for the gateway.
  • SID specifies the SID of the gateway and matches the SID specified in the listener.ora file of the Oracle Net Listener that is listening for the gateway.
  • HS=OK) specifies that this connect descriptor connects to a non-Oracle system.
Testing connection identifier	
$ tnsping dg4odbc
 
TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 07-NOV-2007 08:05:17
 
Copyright (c) 1997, 2007, Oracle.  All rights reserved.
 
Used parameter files:
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLEDB)(PORT = 1521))) (CONNECT_DATA = (SID= myodbc3)) (HS=OK))
OK (0 msec)

Configuring the Gateway

An instance of the gateway is needed for ODBC data source which can be created by copying $ORACLE_HOME/hs/admin/initdg4odbc.ora file. In this scenario copied to initmyodbc3.ora.
$ vi $ORACLE_HOME/hs/admin/initmyodbc3.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
 
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=myodbc3
HS_FDS_TRACE_LEVEL=on
HS_FDS_SHAREABLE_NAME=/usr/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
# set


Step 6: Test connection

You must create a database link to access the ODBC data source. A public database link is the most common of database links.

SQL> CREATE PUBLIC DATABASE LINK mysql
 CONNECT TO oracle
 IDENTIFIED BY "123"
 USING 'DG4ODBC';

SQL> SELECT table_name FROM all_tables@mysql;

TABLE_NAME
------------------------------
tester

If you got any error specifying error specifying syntax as below, it might be due to the sql_mode in MySQL database.

ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[MySQL][ODBC 3.51 Driver][mysqld-4.1.20]You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right
syntax to use near '"ALL_TABLES"' at line 1
ORA-02063: preceding 2 lines from MYSQLDB

For this resolution, you can execute following to change the sql_mode to asni_quotes at session level.

DECLARE
    ret INTEGER;
    c INTEGER;
BEGIN
    c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@mysqldb;
    DBMS_HS_PASSTHROUGH.PARSE@mysqldb(c, 'SET SESSION SQL_MODE=ANSI_QUOTES');
    ret := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@mysqldb(c);
    dbms_output.put_line(ret ||' passthrough output');
    DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@mysqldb(c);
END;

Cheers !!!

Your comments are highly appreciated