Having the following scenario: a simple telephony application "writes" to a Mysql BD and a BD Oracle needed from time to time to look for information in this BD MySql.
Has anyone done this yet?
What would be the best practice?
ps Actually we can not unify because they are products from different vendors, in Oracle we have some maneuverability to do this update, which comes to be: write the Attendance (Mysql) protocol in an Oracle table.
The ideal solution depends on several questions, such as Oracle version, table size, query complexity, refresh rate, and developer technical capability. It is necessary to establish these metrics before defining the most appropriate solution.
For relatively small tables, I would schedule a dump in MySQL, exporting the data to a CSV on disk. Oracle can read the CSV directly, without having to import the data, through a feature called external table .
As you said that the application is "simple" and that Oracle looks for data in MySQL "from time to time", it seems to me that the above solution is suitable for simple implementation.
As the time interval shortens and / or the data volume increases, the outer table solution may be inadequate. In such cases, a more appropriate solution would be the direct link between Oracle and MySQL.
Oracle has a feature called "Database Link", which allows a database (local) to execute SQL commands on another (remote) database. When the local and remote databases are both in Oracle, the process is quite simple.
Already when the remote database is in another platform (in your case, MySQL), you must configure an ODBC connection on the local server. Oracle can read data via ODBC and thereby access MySQL in real time.
Remember, however, that connections via ODBC do not have the same scalability of native connections. Again, it depends on the volume of data and concurrency of the queries.
