This discussion is archived
6 Replies Latest reply: Feb 29, 2012 12:39 PM by 84959 RSS

export Oracle data in MySQL through PHP

917080 Newbie
Currently Being Moderated
HI,

Can anybody tried to export Oracle data & fed it to MySQL db through PHP coding?
Plz guide me or tell me how we can achieve this?

I have write code in php to connect to both Mysql & Oracle databases. Now want to move Oracle data into Mysql (using PHP)..

Plz help me..
Thanks..
  • 1. Re: export Oracle data in MySQL through PHP
    cj Employee ACE
    Currently Being Moderated
    How much data? What constraints do you have on the move? What migration strategies have you already evaluated?
  • 2. Re: export Oracle data in MySQL through PHP
    917080 Newbie
    Currently Being Moderated
    Hi,

    Data is not much. but We have to sync both database thr' PHP.
    We check other migration strategies like migration tool/software, db link etc. but as we want whole process automatic & not manual import/export.

    So best possible option for us to connect Mysql n Oracle through PHP.

    So I am newbie to PHP...so wanted help in that way...
  • 3. Re: export Oracle data in MySQL through PHP
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    You say you have investigated using database links but you could use the Database Gateway for ODBC (Dg4ODBC) in SQL statements in your PHP coding to move data from Oracle to MYSQL.
    Have a look at this note in My Oracle Support -

    Issuing Selects from PHP Using DG4ODBC Does Not Give Correct Results And Error - Ora-1002 (Doc ID 753321.1)

    which gives an example. You could have code in the $SQL section which is this case is -

    $sql = "select id from dbo.msql_table@dblink"; //sql statement in quotes because it's a string as variable

    to copy data from Oracle to MySQL instead of the select in this example.
    If you use this route then check any statements work from SQLPLUS before trying using PHP code.

    Regards,
    Mike
  • 4. Re: export Oracle data in MySQL through PHP
    cj Employee ACE
    Currently Being Moderated
    There will be two main classes of issue:

    1. Data management questions regarding performance, storage, locking etc.
    Some planning and benchmarking will be needed.

    2. Data type conversion questions. Once you have decided on the
    mapping, PHP will help you here.

    There are many resources for PHP & MySQL available to pick from. If
    you're new to PHP OCI8 look at this free book on PHP and Oracle
    and any of the commercially available books on Oracle DB and PHP.
  • 5. Re: export Oracle data in MySQL through PHP
    cj Employee ACE
    Currently Being Moderated
    Also, there's an OTN forum for PHP questions: http://www.oracle.com/technetwork/forums/php
  • 6. Re: export Oracle data in MySQL through PHP
    84959 Explorer
    Currently Being Moderated
    If you already have code for connecting to both databases in PHP, all you need to do is:

    1. Select data from Oracle into arrays or variables based on timestamp that you are looking for.
    2. Insert data from step #1 into MySQL using the mysql connection.

    It is very simple. Not sure what the complication is?. It is a simple SELECT and then INSERT Into mysql and commit. Alternatively you can use database jobs in oracle to insert data into MySQL periodically using ODBC Gateways based DBLinks. Performance won't be any worse than you selecting data from Oracle into your application and then writing it out to MySQL.

    Regards

    Prakash

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points