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