Skip to Main Content
  • Questions

  • Taking advantage of rich oracle sql features for data in mysql

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, stephen dedalus.

Asked: January 09, 2014 - 10:35 am UTC

Last updated: January 10, 2014 - 8:50 pm UTC

Version: 12

Viewed 1000+ times

You Asked

Hi Tom,

I read the AskTom column in Oracle magazine ( NOV/DEC 2013 ) and was totally impressed with the row pattern matching ability in oracle 12c .

My question is that assuming one is using Oracle as well as MYSQL in a company for different purposes and there are scenarios in which rich sql feature set of oracle ( Example Row pattern matching / Analytic functions ) needs to be used for tables residing in mysql database. What is the easiest/quickest/best way of doing this ? ( assuming data volume is very small )

We can of-course make a dump of a table from mysql into a file and make it as an external table in oracle ( or better create and load into a new table in oracle ) .

But the problem with this approach is that if there are many tables in mysql , we need to load each one and we need to refresh it every time the data changes in mysql.

Is there a way where we can kind of "mount" tables belonging to particular schema in mysql into oracle and have a read-only access to it so that we need not migrate the data from mysql every time and run some queries on these tables from oracle.

I am not sure if the question seems naive but I was wondering if this is possible.

Also read about your resolution of trying to use Row pattern matching to solve questions in asktom and I am eagerly waiting for it :)

Also Have you decided which "new technology outside the comfortable realm of Oracle Database" you are planning to learn ? ( Just out of curiosity )

Thanks for the service you are doing to oracle community.

Regards,
Stephen

and Tom said...

the referenced article is:
http://www.oracle.com/technetwork/issue-archive/2013/13-nov/o63asktom-2034271.html



... What is the easiest/quickest/best way of doing this ? ...

database links.

for example:
http://www.pythian.com/blog/how-to-access-mysql-from-oracle-with-odbc-and-sql/



The new technology will almost certainly be something more in the area of "big data" related. It'll obviously have a tie back to Oracle :) Something in the area of sharing between traditional RDBMS and not RDBMS - and of course, looking for areas where dumping the data out of the RDBMS into something else just isn't necessary (look at row pattern matching... if I can do that in SQL, in parallel, why do I need to push things out to another database?)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks...

stephen dedalus, January 10, 2014 - 9:17 pm UTC

Thanks a lot for the answer Tom. Eagerly waiting for someone like you(credibility) to answer user queries in BigData / Hadoop space. It would be great if you can do it yourself :)

Regards,
Stephen
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.