Recent blog posts
- PHP array to html UL > LI Hirarchy
- Oracle Services Quick Notes
- ORA-19566: exceeded limit of 0 corrupt blocks for file
- Mysql Date Range Query
- ORA-29701: Unable to connect to Cluster Manager
- Extract days hours minutes seconds from two dates in oracle
- ORA-00845: MEMORY_TARGET not supported on this system
- Killing Oracle Session
- Copy MySQL database to remote server
- ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 960
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 220.127.116.11.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> 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.
[ODBC Data Sources]
myodbc3 = MySQL ODBC 3.51.23 Driver DSN
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
- 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.
| Connected! |
| sql-statement |
| HELP [tablename] |
| quit |
SQL> CREATE TABLE tester(id INT, name VARCHAR(24));
Step 5: Prepare Oracle
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.
Now reload the listener utility to load the changed configuration to listener.ora file.
$ lsnrctl stop $ lsnrctl start $ lsnrctl status
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 18.104.22.168.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 GatewayAn 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.
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
# HS init parameters
# ODBC specific environment variables
# Environment variables required for the non-Oracle system
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.
CONNECT TO oracle
IDENTIFIED BY "123"
SQL> SELECT table_name FROM all_tables@mysql;
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.
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');
Your comments are highly appreciated
Feedback to: firstname.lastname@example.org
© Copyright 2007 birijan.com.np, All Rights Reserved
We are not responsible for any loss or liability incurred by using this information.