How to connect from oracle to mysql

Two products - DG4MSQL and DG4ODBC.

a) DG4ODBC is for free and requires a 3rd party ODBC driver
and it can connect to any 3rd party database as long as you use a suitable ODBC driver

b) DG4MSQL is more powerfull as it is designed for MS SQL Server databases and it supports many functions it can directly map to SQL Server equivalents - it can also call remote procedures or participtae in distributed transactions. Please be aware DG4MSQL requires a license - it is not for free.

==============================================================
Here I user use ODBC for connect from oracle database to Mysql
connectivity to MYSQL via a database link and heterogeneous services.
==============================================================

Oracle version is 10.2.0.1.0 on Windows Server 2003

1.

In windows control panal > administration tools > data source odbc > SYSTEM DSN

I create a SYSTEM DSN to a SQL Server 2000.The DSN tests "successfully".
Data source name : mysql
Descreption : (by default)
server : localhost
user : root
passward : password of root

Then click test button.

2.

I created a file called initmysql.ora under oracle_home/hs/admin
with this in it:

HS_FDS_CONNECT_INFO = mySql
HS_FDS_TRACE_LEVEL = 0


3. I added this to the tnsnames.ora file

mysql=
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.11.1.248)(PORT=1521)
) (CONNECT_DATA= (SID=mysql)
) (HS=OK)
)


4. I added this to the listerner.ora file:
(under the "SID_LIST_LISTENER =" portion)

(SID_DESC=
(SID_NAME=mysql)
(ORACLE_HOME=G:\oracle_new\DB10G2\APP1\BEFTN)
(PROGRAM=hsodbc)
(ENVS=LD_LIBRARY_PATH = G:\oracle_new\DB10G2\APP1\BEFTN\lib)
)
--------------------------------------listerner look like------------
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.1.248)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = G:\oracle_new\DB10G2\APP1\BEFTN)
(PROGRAM = extproc)
)
(SID_DESC=
(SID_NAME=mysql)
(ORACLE_HOME=G:\oracle_new\DB10G2\APP1\BEFTN)
(PROGRAM=hsodbc)
(ENVS=LD_LIBRARY_PATH = G:\oracle_new\DB10G2\APP1\BEFTN\lib)
)
)
----------------------------------------------------------------------------

5 After this you need to do
a) STOP and START the listener . show LSNRCTL> SERVICES
B) CMD> tnsping mysql (tns name ) result is ok


6. Then I created the database link

CREATE DATABASE LINK "MYSQL.REGRESS.RDBMS.DEV.US.ORACLE.COM"
CONNECT TO "root" (MUST NEED TO GIVE "" , NOT IN PASSWORD)
IDENTIFIED BY
USING 'mysql'; -----TNS NAME

Then test it with toad . successful

complete your connectivity--------
==========================================================================

Some testing.............and solution

7. open sqlplus run following block . if it success then connection establish.

for checking
=============

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

8. If You face

problem:
==============
Error: ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][unixODBC][TCX][MyODBC]Access denied for user 'oracleuser'@'localhost'
(using password: YES) (SQL State: S1000; SQL Code: 1045)
ORA-02063: preceding 2 lines from MYSQL

Solution
============
mysql> SET PASSWORD FOR
-> [email='some_user'@'some_host'] = OLD_PASSWORD(’newpwd’);
Alternatively, use UPDATE and FLUSH PRIVILEGES:

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD(’newpwd’)
-> WHERE Host = ’some_host’ AND User = ’some_user’;
mysql> FLUSH PRIVILEGES;