This is really cool! Can something be done with this to use a materialized view log on a table in Oracle and sync with a table in mysql?
-----Original Message----- From: Mark Leith [mailto:[EMAIL PROTECTED] Sent: Friday, September 16, 2005 9:54 AM To: mysql@lists.mysql.com Subject: RE: database link 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] _______________________________________________________ This message has been scanned for viruses by TechTeam's email gateway. _________________________________________________ This e-mail transmission is strictly confidential and intended solely for the person or organization to whom it is addressed. It may contain privileged and confidential information and if you are not the intended recipient, you must not copy, distribute or take any action in reliance on it. If you have received this e-mail in error, please notify the sender as soon as possible and delete the e-mail message and any attachment(s). This message has been scanned for viruses by TechTeam's email gateway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
