MySQL Forums
Forum List  »  Oracle

Re: Heterogenous replication between Oracle 9i and MySQL
Posted by: Mickael Besson
Date: March 23, 2006 08:06AM

Hello Jerry,

I don't know if can use views to connect directly MySQL to Oracle.

Nevertheless, you can create a batch which export data from Oracle and import in MySQL.

Scripts I use to replicate data through the two DB :

1/ Export Oracle (exportQuery.txt)
________________

whenever oserror exit oscode
whenever sqlerror exit sql.sqlcode

set term off
set newpage 0
set space 0
set linesize 1000
set pagesize 0
set echo off
set feedback off
set heading off
set COLSEP ' '
set ver off

spool myTable.txt
SELECT field1, field2,TO_CHAR(field2,'YY/MM/DD')...
FROM MYSHEMA.MYTABLE
ORDER BY PRYMAIREKEY;
/** for ex. here, third argument is a date and must be casted **/

spool off

EXIT


2/ Import MySQL (importQuery.sql)
_______________

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO' */;

LOAD DATA INFILE 'myTable.txt' REPLACE INTO TABLE SCHEMA.MYTABLE;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;


3/ Batch .BAT to run on Windows(containing import and export)
_____________________________________________________________

:begin
@ECHO off
cls

ECHO.
ECHO.
ECHO *****************
ECHO * Export Oracle *
ECHO *****************
ECHO.

sqlplus -s login/password@schema @exportQuery
ECHO.
ECHO RETURN CODE : %ERRORLEVEL%

ECHO.
ECHO.
ECHO *****************
ECHO * Import MySQL *
ECHO *****************
ECHO.

mysql mytable < importQuery.sql -u root -ppassword

ECHO.
ECHO RETURN CODE : %ERRORLEVEL%

ECHO.
ECHO *** END OF BATCH ***


Try to use portable data types on Oracle and MySQL.
http://home.fnal.gov/~dbox/SQL_API_Portability.html

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Heterogenous replication between Oracle 9i and MySQL
5968
March 23, 2006 08:06AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.