You may want to think about doing this the opposite way around also, and
look at pushing the data from Oracle in to MySQL. Oracle has something
called "heterogeneous services", which allows you to define ODBC datasources
as valid entries in the tnsnames.ora file. Then you could simply create a
job in Oracle that executes a procedure to do the entire process (truncate /
load), no external scripting necessary..
Here's a quick example of what to do:
First set up an ODBC data source for your MySQL database, using MyODBC.
Create a file in ORACLE_HOME/hs/admin called initMySQL.ora. In this file put
the following options:
HS_FDS_CONNECT_INFO = MySQL5 #ODBC DSN /* Replace MySQL5 with your DSN */
HS_FDS_TRACE_LEVEL = OFF
Alter your listener.ora file (ORACLE_HOME/network/admin) to add the
following:
(SID_DESC =
(PROGRAM = hsodbc)
(ORACLE_HOME = oracle/product/92) /* Your ORACLE_HOME */
(SID_NAME = MySQL5) /* Your DSN */
)
Add the following to your tnsnames.ora file:
MYSQL5 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=MYSQL5))
(HS=OK)
)
Reload your Oracle listener (lsnrctl reload), and then connect to the Oracle
database. To set the database link up:
CREATE DATABASE LINK mysql5 CONNECT TO "user" identified by "password" using
'mysql5';
User and password should be a valid user within MySQL, that can connect from
the Oracle host. You should be set to go from there. Here's a quick example
of this working, to a MySQL 5 database using the new "sakila" sample
database that Mike Hillyer recently released
(http://www.openwin.org/mike/download/sakila.zip):
SQL> select count(*) from [EMAIL PROTECTED];
COUNT(*)
----------
1000
SQL> desc [EMAIL PROTECTED];
Name Null? Type
----------------------------------------- --------
----------------------------
film_id NUMBER(10)
category_id NOT NULL NUMBER(10)
title NOT NULL VARCHAR2(27)
description LONG
rental_duration NOT NULL NUMBER(3)
length NUMBER(10)
rating CHAR(5)
SQL> insert into [EMAIL PROTECTED] values (1000000,1,'test','test',1,1,'PG');
1 row created.
---change prompts---
mysql> use sakila
Database changed
mysql> select max(film_id) from film;
+--------------+
| max(film_id) |
+--------------+
| 1000000 |
+--------------+
1 row in set (0.01 sec)
HTH
Mark Leith
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 15/09/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
