Hi Mark, First set up an ODBC data source for your MySQL database, using MyODBC. How do i do this. regards anandkl On 9/19/05, Ananda Kumar <[EMAIL PROTECTED]> wrote: > > Hi Mark, > Thanks for the email. Yes i also read about this. > But the problem with this approach is since our oracle db is a production > database which runs 24*78 and during night time it runs lot of other jobs i > cannot make the reporting job run from this oracle database. > regards > anandkl > > On 9/16/05, Mark Leith <[EMAIL PROTECTED]> wrote: > > > > 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] > > > > >
