1 2 3 Previous Next 41 Replies Latest reply: Jul 5, 2012 6:33 AM by user1888509 RSS

    How to connect Oracle DB to Mysql DB

    user1888509
      Hi All,

      I need to read data from oracle database to mysql DB.
      Any body can help the step how to read mysql data from oracle DB ?

      Thanks for your help.


      Regards,
      Iwanto
        • 1. Re: How to connect Oracle DB to Mysql DB
          Mkirtley-Oracle
          Hi Iwanto,
          You can to use the 11g Database Gateway for ODBC (DG4ODBC) to connect from Oracle to MySQL.
          The following notes available in My Oracle Support have more details -

          Master Note for Oracle Gateway Products (Doc ID 1083703.1)

          - this note gives general information about the Gateways.

          You don't say on which platform you are running but the following notes describe the DG4ODBC setup on different platfroms -

          How to Setup DG4ODBC on Linux x86 32bit (Doc ID 466228.1)
          How to Setup DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) (Doc ID 561033.1)
          How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit (Doc ID 466225.1)
          How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems (Doc ID 1266572.1)

          Gateway Configuration Utility for Database Gateway for ODBC - DG4ODBC - to Connect to Non-Oracle Databases For Example - DB2, SQL*Server, Sybase, Informix, MySQL (Doc ID 1274143.1)

          Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (Doc ID 1320645.1)

          Please review these notes and get back if you have any further questions.

          Regards,
          Mike
          • 2. Re: How to connect Oracle DB to Mysql DB
            user1888509
            Hi Mike,

            Thanks for your info,

            I am running from windows XP,i use oracle 10g Database.

            i have followed below instruction :

            https://hs2n.wordpress.com/2012/04/03/oracle-create-database-link-to-mysql-database/

            I got below Error warning.

            SQL> /
            select * from nama@link2mysqltest
            *
            ERROR at line 1:
            ORA-28545: error diagnosed by Net8 when connecting to an agent
            Unable to retrieve text of NETWORK/NCR message 65535
            ORA-02063: preceding 2 lines from LINK2MYSQLTEST


            Anybody can help?

            Thanks and regards,
            Iwanto
            • 3. Re: How to connect Oracle DB to Mysql DB
              Mkirtley-Oracle
              Iwanto,
              The ORA-28545 error is usually because of configuration problems.
              in the link youhave posted there will be a problem with the configuration files. They look like this -

              SID_LIST_LISTENER =
              (SID_LIST=
              (SID_DESC=
              (SID_NAME=tst)
              (ORACLE_HOME=C:\oracle\product\11.2.0\dbhome_1)
              (PROGRAM=dg4odbc)
              )
              )

              and

              TSTLINK =
              (DESCRIPTION=
              (ADDRESS=
              (PROTOCOL=TCP)
              (HOST=localhost)
              (PORT=1521)
              )



              but there should be at least one space at the beginning of every line except the first -

              SID_LIST_LISTENER =
              <space> (SID_LIST=
              <space> (SID_DESC=
              <space> (SID_NAME=tst)
              <space> (ORACLE_HOME=C:\oracle\product\11.2.0\dbhome_1)
              <space> (PROGRAM=dg4odbc)
              <space> )
              <space> )

              and -

              TSTLINK =
              <space> (DESCRIPTION=
              <space> (ADDRESS=
              <space> (PROTOCOL=TCP)
              <space> (HOST=localhost)
              <space> (PORT=1521)
              <space> )
              <space> (CONNECT_DATA=
              <space> (SID=tst)
              <space> )
              <space> (HS=OK)
              <space> )

              We aren't responsible for any non-Oracle articles about setting up our products.

              Also, this and the Oracle notes describe a 11g DG4ODBC setup. If your RDBMS is 10.2 then you need to install DG4ODBC into a completlely separate and new ORACLE_HOME from the existing 10.2 install. The DG4ODBC listener should run from this OH and the tnsnames.ora entry needs to be in the 10.2 directory.

              If you post your configuration files then we can see what is wrong.

              Regards,
              Mike

              Edited by: mkirtley on Jun 29, 2012 12:56 PM
              • 4. Re: How to connect Oracle DB to Mysql DB
                user1888509
                Hi Mike,

                below were my setup

                0. Install mySql ODBC

                1. LISTENER.ora
                ==================
                SID_LIST_LISTENER =
                (SID_LIST =
                (SID_DESC =
                (SID_NAME = PLSExtProc)
                (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
                (PROGRAM = extproc)
                )
                )

                LISTENER =
                (DESCRIPTION_LIST =
                (DESCRIPTION =
                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                (ADDRESS = (PROTOCOL = TCP)(HOST =abc123)(PORT = 1521))
                )
                )


                SID_LIST_LISTENER =
                (SID_LIST =
                (SID_DESC =
                (SID_NAME = tst)
                (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
                (PROGRAM = dg4odbc)
                )
                )
                ==================

                2. in this folder D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN, i crreated 1 file "inittst.ora" and in the file i write "HS_FDS_CONNECT_INFO = test"

                3. Added TNS NAMe.ora
                ===================
                TSTLINK =
                (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = abc123)(PORT = 1521))
                (CONNECT_DATA =
                (SID = tst)
                )
                (HS=OK)
                )
                ===================

                4.Restart the Oracle Service and the TNS Listener Service
                5. create public database link link2mysqltest connect to "oracle" identified by "oracle" using 'TSTLINK';
                6. select * from "test_table"@link2mysqltest
                ---i got the error msg---
                ERROR at line 1:
                ORA-28545: error diagnosed by Net8 when connecting to an agent
                Unable to retrieve text of NETWORK/NCR message 65535
                ORA-02063: preceding 2 lines from LINK2MYSQLTEST


                Thanks for your help Mike.

                Regards,
                Iwanto
                • 5. Re: How to connect Oracle DB to Mysql DB
                  Kgronau-Oracle
                  DG4ODBC is a gateway shipped with Oracle 11, but your listener indicates a 10.2 Oracle_Home:
                  (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
                  (PROGRAM = dg4odbc)

                  Did you install the 11g DG4ODBC software into an Oracle 10.2 database home?

                  Please be aware mixing Oracle 11g and 10g software will cause trouble as DLLs with the same name are overwritten. So when you have accidentally installed 11g software into a 10g database home, please deinstall the 11g software and then reinstall the 10g software again (including all patches) just to make sure you have all required 10g DLL's installed. Then install the 11g software into its OWN, separate Oracle_Home differet from the 10g database home.

                  Another point to clarify:
                  2. in this folder D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN, i crreated 1 file "inittst.ora" and in the file i write "HS_FDS_CONNECT_INFO = test"
                  The gateway init file needs to be in the ORACLE_HOME\hs\ADMIN directory - not in network\admin.
                  • 6. Re: How to connect Oracle DB to Mysql DB
                    user1888509
                    Hi, Thanks for your info.

                    May i know where can i download 11g DG4ODBC ?

                    I have not installed 11g software.

                    Thanks and regards,
                    Iwanto
                    • 7. Re: How to connect Oracle DB to Mysql DB
                      Kgronau-Oracle
                      You can download it from OTN:
                      http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
                      Choose your platform (32/64bit Windows) and then select the Gateway CD (Oracle Database Gateways 11g Release 2 )

                      The 11.2 Gateway is certified with Oracle database release 10.2.0.4 and 10.2.0.5 (as well as 11g), so please make sure that your Oracle database has the latest 10.2 patch set installed.
                      • 8. Re: How to connect Oracle DB to Mysql DB
                        user1888509
                        Hi, i have installed oracle gateway 11g already.

                        I got this message :
                        ERROR at line 1:
                        ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                        [Microsoft][ODBC Driver Manager] Data source name not found and no default
                        driver specified {IM002}
                        ORA-02063: preceding 2 lines from LINK2MYSQLTEST


                        HS_FDS_CONNECT_INFO = test ==> test is the ODBC name right ?


                        Thanks and regards,
                        Iwanto
                        • 9. Re: How to connect Oracle DB to Mysql DB
                          Kgronau-Oracle
                          test is the name of a SYSTEM DSN created using the ODBC Administrator
                          • 10. Re: How to connect Oracle DB to Mysql DB
                            Mkirtley-Oracle
                            Iwanto,
                            Also, are you running on 32-bit or 64-bit Windows ? If it is 64-bit then you need a 64-bit ODBC driver as you can't use a 32-bit driver with 64-bit DG4ODBC.

                            Regards,
                            Mike
                            • 11. Re: How to connect Oracle DB to Mysql DB
                              user1888509
                              I have tested from ODBC and connected succuessfully.

                              I use tnsping, and i got connected.

                              when i tried to select using dblink, that message appear.


                              i used 32 bit window, so i installed 32 bit oracle gateway.

                              Thanks,
                              Iwanto
                              • 12. Re: How to connect Oracle DB to Mysql DB
                                Kgronau-Oracle
                                Is the ODBC DSN a SYSTEM DSN?
                                Maybe you can post the registry output:
                                HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
                                • 13. Re: How to connect Oracle DB to Mysql DB
                                  user1888509
                                  Below is the value of the registry

                                  Inside ODBC.ini, there is a folder called BPKB

                                  In the folder contain :

                                  DATABASE     BPKB123
                                  DESCRIPTION     BPKB
                                  DRIVER          D:\WINDOWS\system32\myodbc5.dll
                                  PORT          3306
                                  SERVER          172.18.1.100
                                  UID          root

                                  Thanks and regards,
                                  Iwanto
                                  • 14. Re: How to connect Oracle DB to Mysql DB
                                    Kgronau-Oracle
                                    then the name is not test - it is BPKB

                                    Edited by: kgronau on Jul 3, 2012 11:52 AM

                                    Change HS_FDS_CONNECT_INFO=test to HS_FDS_CONNECT_INFO=BPKB
                                    1 2 3 Previous Next