List:General Discussion« Previous MessageNext Message »
From:Mark Leith Date:September 16 2005 1:54pm
Subject:RE: database link
View as plain text  
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 film@mysql5;

  COUNT(*)
----------
      1000

SQL> desc film@mysql5;
 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 film@mysql5 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
 

Thread
database linkAnanda Kumar15 Sep
  • Re: database linkSGreen15 Sep
RE: database linkMikhail Berman15 Sep
Re: database linkSGreen15 Sep
  • RE: database linkMark Leith16 Sep
    • Re: database linkAnanda Kumar19 Sep
    • Re: database linkAnanda Kumar19 Sep
      • Re: database linkAnanda Kumar20 Sep
Re: database linkSGreen15 Sep