Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Coimbra, Portugal

    Unanswered: Syncronizing two databases in Oracle and MySQL


    I'm working on a project where I have to retrieve a large amount of data from two Oracle tables in Server B, and use it to update a mirror of those same tables in a mysql database of Server A.

    Both servers are connected in a LAN network.

    The project behind these two databases is a proprietary warehouse management system and I can only read those two Oracle tables. No write operations are allowed. Each time a new object is added to the warehouse, I have to update the mysql database.

    Well, the first approach (and the worst too) I can think of is to do a massive dump of all the records of Server B and, one by one, update them in the mysql database. We're talking about 300.000 records. Yes. It's a very large warehouse.

    The whole problem is that I don't have the "know how" to syncronize both databases.

    Can anyone help me?

    If both databases were in Oracle, it was very simple. One trigger could do the job.

    Thanks in advance,
    Bruno Ferreira

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    The solution is just a Small Matter Of Programming (SMOP).
    I'd write a trigger in PL/SQL which would use UTL_TCP
    to send the changed data out to an open socket connection.
    I'd have a PERL program which would collect the data from
    the soccket connection and use DBD/DBI to INSERT/UPDATE
    the MySQL database.
    It would likely take less than a couple of dozen lines of code
    for each part.
    Alternatively use UTL_FILE to write out the changed data,
    and use either PERL or the native MySQL "bulk load" utility
    to move the data in MySQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2003
    Coimbra, Portugal
    Hmm.. It might just do the job

    Thanks for your help!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts