Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Database link from Oracle to MySQL

Roel HartmanNov 2 2010 — edited Nov 3 2010
I tried and googled an tried again...but I can't get this to work. So if you can spare a moment, please help me out (or try to ;-) ).

Environment:
The Oracle Developer Days Virtual Box image, so that's 11gR2 EE on OEL. MySQL 5.1.51-community + mysql-connector-odbc-5.1.7-0.i386.rpm

1. Created a database called test in MySQL, with a user "oracle" and a password "123".

2. Created the file /home/oracle/.odbc.ini with as contents:
[ODBC Data Sources]
myodbc5 = MySQL ODBC 5.1.7 Driver DSN
[myodbc5]
DRIVER      = /usr/lib/libmyodbc5.so
DESCRIPTION = MySQL ODBC 5.1.7 Driver DSN
SERVER      = 127.0.0.1
PORT        = 3306
USER        = oracle
PASSWORD    = 123
DATABASE    = test
CHARSET     = latin1
3. Created the file /home/oracle/app/oracle/product/11.2.0/dbhome_1/hs/admin/initmyodbc5.ora
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=myodbc5
HS_FDS_TRACE_LEVEL=DEBUG
HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc5.so
HS_LANGUAGE=AMERICAN.AMERICA.WE8ISO8859P15
HS_FDS_SUPPORT_STATISTICS=FALSE
# HS_NLS_NCHAR=AL32UTF8
#
# ODBC specific environment variables
#
set ODBCINI=/home/oracle/.odbc.ini
set ODBCINSTINI=/home/oracle/.odbc.ini
set LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/hs/lib:/usr/lib
#
# Environment variables required for the non-Oracle system
#
set HOME=/home/oracle
4. Added to tnsnames.ora :
dg4odbc =
  (DESCRIPTION = 
    (ADDRESS_LIST = 
       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA = (SID=myodbc5))
    (HS=OK)
  )
5. Added to listener.ora (and restarted it) :
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = myodbc5)
      (PROGRAM = dg4odbc)
      (ENVS ="LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/hs/lib:/usr/lib:/lib")
    )
  )
6. Ran some tests:
a. isql dg4odbc -> Connected!
b. tnsping dg4odbc -> OK
c. lsnrctl status -> Service "myodbc5" has 1 instance(s). Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
d. in SQLPLUS (as system): create public database link mysql connect to "oracle" identified by "123" using 'dg4odbc';
e. select * from test@mysql ("test" is a simple table I created in the MySQL DB) returns
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from MYSQL

When I look in the trace file (in $ORACLE_HOME/hs/log) I see at the bottom of the file:
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib/libmyodbc5.so"
Entered hgolofns at 2010/11/02-14:10:54
 hoaerr:28500
Exiting hgolofns at 2010/11/02-14:10:54
Failed to load ODBC library symbol: /usr/lib/libmyodbc5.so(SQLAllocHandle)
Exiting hgolofn, rc=28500 at 2010/11/02-14:10:54
Exiting hgoinit, rc=28500 with error ptr FILE:hgoinit.c LINE:337 FUNCTION:hgoinit() ID:Loading ODBC aray of function ptrs
Entered hgoexit
HS Gateway:  NULL connection context at exit
Exiting hgoexit, rc=0 with error ptr FILE:hgoexit.c LINE:108 FUNCTION:hgoexit() ID:Connection context
After Googling around I found out that all files are 32-bit (was on issue somewhere else)...and now I am stuck!

Any ideas, tips etc on how to proceed???

TIA
Roel
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 1 2010
Added on Nov 2 2010
13 comments
6,946 views