Sometimes you need to access data stored in an different RDBMS like MySQL. Oracle provides an convenient way to access external data-sources via ODBC. To use Oracle HSODBC you can follow my step-by-step notes:
- Install unixODBC and the MySQL ODBC Driver
For Fedora Core 6 I installed the following RPMs:- unixODBC-2.2.11-7.1
- mysql-connector-odbc-3.51.12-2.2
- Configure /etc/odbc.ini
[ODBC Data Sources] TimeSheetTest = MySQL ODBC PHP.TimeSheetTest DSN [TimeSheetTest] Driver = /usr/lib/libmyodbc3.so Description = MySQL ODBC 3.51 Driver DSN SERVER = localhost PORT = 3306 USER = root Password = SECRET Database = timesheet OPTION = 3 SOCKET =
- Test the ODBC Connection via isql
[frank@w0004]$ isql TimeSheetTest Connected! sql-statement help [tablename] quit SQL>
- Oracle Configuration
First ensure that the shell environment used to start the Oracle listener includes the following
environment variables:export ODBCINI=/etc/odbc.ini export ODBCSYSINI=/etc
To integrate that ODBC connection into Oracle you have to edit several configuration files:
$ORACLE_HOME/hs/admin/initTimeSheetTest.ora
HS_FDS_CONNECT_INFO = TimeSheetTest HS_FDS_TRACE_LEVEL = off HS_FDS_SHAREABLE_NAME = /usr/lib/libmyodbc3.so
$ORACLE_HOME/network/admin/listener.ora
... SID_LIST_LISTENER = ... (SID_DESC = (PROGRAM = hsodbc) (ORACLE_HOME = /opt/oracle/ora10g) (SID_NAME = TimeSheetTest) (ENVS=LD_LIBRARY_PATH = /usr/lib:/opt/oracle/ora10g/lib) ) ) ...
$ORACLE_HOME/network/admin/tnsnames.ora
... TIMESHEET = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SID= TimeSheetTest))(HS=OK) ) ...
- Test the Configuration with tnsping
$ tnsping timesheet TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 05-SEP-2007 18:26:16 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SID= TimeSheetTest))(HS=OK)) OK (10 msec)
- Create an Oracle Link in your Oracle Database instance
SQL> CREATE PUBLIC DATABASE LINK timesheet CONNECT TO "root" IDENTIFIED BY "SECRET" USING 'TIMESHEET';
Please note that MySQL is case-sensitive on tablenames, so you have to use:
SQL> select * from "timesheet_times"@timesheet; ...