orcel向mysql推送数据,在MySQL和Oracle数据库之间传输数据

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