Forum Stats

  • 3,741,184 Users
  • 2,248,385 Discussions
  • 7,861,667 Comments

Discussions

Conenct Oracle Database to MySql Community

sistemas_GP
sistemas_GP Member Posts: 1
edited Jun 27, 2018 1:02AM in Oracle Call Interface (OCI)

Hi,

I need to select data from a MySQL database table for Oracle Database.

How do I make the connection?

Someone can explain to me what I have to do.

Thanks.

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,777 Bronze Crown
    edited Jun 27, 2018 1:01AM

    Oracle Database Gateways (also known as Oracle Heterogeneous Services - HS)

    . Oracle Database Gateways

    Oracle database comes with dg4odbc - Database Gateway using ODBC and  Oracle database link.

    You will need:

    . mysql ODBC driver (https://dev.mysql.com/downloads/connector/odbc/ )

    . An ODBC connection for connecting to mysql

    . An Oracle listener set up with access to ODBC software..

    . An Oracle  listener set up to listen for HS connection requests.

    . tnsnames.ora on the listener server with a connection string set up for HS.

    . A db_link that makes use of the tnsnames.ora HS connection.

    Now you can write sql :select * from [email protected];", or even execute native mysql functions via DBMS_HS_PASSTHROUGH PL/SQL package:

    declare   c binary_integer;   r binary_integer := 0;   v varchar2(30);begin   dbms_output.new_line;   dbms_output.put_line('BANNER (Oracle Version)');   dbms_output.put_line(RPAD('-', 74, '-'));   for r in (SELECT * FROM v$version) loop      dbms_output.put_line(r.banner);   end loop;   dbms_output.new_line;   dbms_output.put_line('mysql Version');   dbms_output.put_line(RPAD('-', 74, '-'));   /*Access mysql native function version() */   c := [email protected];   [email protected](c, 'select version()');   r := [email protected](c, false);   [email protected](c, 1, v);   [email protected](c);   dbms_output.put_line('mysql version: '||v);end;/BANNER (Oracle Version)--------------------------------------------------------------------------Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit ProductionPL/SQL Release 11.2.0.2.0 - ProductionCORE    11.2.0.2.0      ProductionTNS for 64-bit Windows: Version 11.2.0.2.0 - ProductionNLSRTL Version 11.2.0.2.0 - Productionmysql Version--------------------------------------------------------------------------mysql version: 5.1.73PL/SQL procedure successfully completed.

    ...for example.

    [Edit: If you have specific HS question, open a thread here:: ]

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,777 Bronze Crown
    edited Jun 27, 2018 1:01AM

    If you don't want to use HS (for what ever reason) you could have 2 connections in your "app". One connecting to Oracle db and one connecting to mysql db.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,777 Bronze Crown
    edited Jun 27, 2018 1:02AM

    If you are wanting to migrate your database from mysql to Oracle Server, then SQL Developer - Database Migration is the way to go.

    SQL Developer Downloads Page

    ...and

    Migrating Third Party Databases

This discussion has been closed.