Oracle DBlink for MySQL

[madmax]

Personnel,

I need to make a dblink of an Oracle 11g bank for a Mysql 5.1 bank.

I searched the dad of the donke and I found several things, but I can not yet this connection.

Someone already did this and could share.

any tip and welcome.

ABS ..

[stcoutinho]

Hello Madmax!

This link below seems very reliable and describes step by step the creation of an Oracle pointing DBLink for MySQL:



Unfortunately I did not have time to reproduce these instructions.

If I can subsequently, I'll post around here, okay?

Hugs,

Sergio Coutinho

[stcoutinho]

Madmax,

The link that I passed you earlier seems to explain how to create an Oracle DBLink for SQL for the Oracle 10G release.

In the case of 11g, the 10G HSODBC program was replaced by DG4DBC.

Basically, I followed the recommendations of the previous link (in Windows) with some changes:

- Creation of the MYSQL5 name drive (I believe an ODBC connection In Windows - the so-called DNS). In this ODBC you inform the user and password (eg root) and then position the desired base (eg sakila);
- I created the initmysql5.ora file in the HS subdirectory (pointing to the MySQL5 driver);
- I configured my listener.ora to accept the DG4DBC.exe program, informing the full path:

Select all

    (SID_DESC = 
      (SID_NAME=MySQL5) 
      (ORACLE_HOME = D:\oracle\product\xe11g\app\oracle\product\11.2.0\server) 
      (PROGRAM=D:\oracle\product\xe11g\app\oracle\product\11.2.0\server\bin\dg4odbc.exe) 
    )

- Set the TNSNAMES. Now to map the MySQL base:

Select all

# Teste de DBLINK com o MYSQL Local 
MySQL5.world = 
  (DESCRIPTION= 
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)) 
    (CONNECT_DATA=(SID=MySQL5)) 
    (HS=OK) 
  )

- I performed the command:

Select all

ALTER SYSTEM SET GLOBAL_NAMES=false;

- I restarted the XE Listener;

- I did a SELECT test in one of the tables. However, the Data fields came with strange characters. It is important to evaluate if something else needs to be configured P (ex: charset) to allow a correct visalization dfdos data from MySQL.

I found additional information on this link: https://forums.oracle.com/thread/2283007


Good luck and post here your solution, if you can successfully configure .

Hugs,

Sergio Coutinho

[madmax]

Great Sergio ...

I was able to do the Oracle DBLink for MySQL following these tips your thanks.

and I will leave an important tip here to convert the Data Timestamp MySQL to Oracle.

and very simple and only do the SELECT

Select all

 select to_date('01-JAN-1970','dd-mon-yyyy')+(SUBSTR(COLUNA_MYSQL,1,10)/60/60/24) DATA_CRIACAO from TABELA_MYSQL@DBLINK

now an explanation as to do this:

has to use this fixed format '01 -jan-1970 'because MySQL Timestamp was created in TimeStamp UNIX format that the number of seconds passed from January 1, 1970 (midnight UTC / GMT), not counting the bisexts (in ISO 8601: 1970-01-01T00: 00: 00z).

I hope you help this post.

ABS ..