Here's the scenario:
I have a MySQL DB, and an Oracle DB, and what I want to do is copy data from MySQL to Oracle, on a scheduled basis.The process includes just inserting data to a table on the Oracle side, taken from th开发者_如何学编程e MySQL db.
Something like this: (oracle command)
insert into my_oracle_table
select * from my_mysql_table@my_mysql_db
where date > sysdate - 7;
What is the best practice for this? Is there a way to connect say to the MySQL db directly from an Oracle stored procedure? Maybe the other way round?
Oracle 11g and MySql 5.1
Create a database link from your Oracle server to the MySQL server.
Once the database link has been established, you can use the standard insert into.. select from
syntax
insert into my_oracle_table
select * from my_mysql_table@dblinkname
where date > sysdate - 7;
If you want to do this on a scheduled basis, use the job scheduler to schedule the transfer.
You can use O.D.I., Oracle Data Integrator.
http://www.oracle.com/technetwork/middleware/data-integrator/index.html
Oracle Data Integrator is a comprehensive data integration platform that covers all data integration requirements: from high-volume, high-performance batch loads, to event-driven, trickle-feed integration processes, to SOA-enabled data services.
精彩评论