6 Replies Latest reply on Feb 29, 2012 8:39 PM by Pnauduri-Oracle

    export Oracle data in MySQL through PHP


      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..
        • 1. Re: export Oracle data in MySQL through PHP
          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

            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
              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.

              • 4. Re: export Oracle data in MySQL through PHP
                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
                  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
                    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.