I have two servers, one is Oracle 9i and the other is 10g. I plan to put a stored procedure in the 10g server that will auto-transfer data into the 9i server daily. How do I write the procedure to connect to the 9i server? Can the stored procedure tranfer data between two servers?
The first thing you'll need to do is to set up a database link which can connect your 10g database to the 9i database. Then pulling data from the 9i database and inserting into the 10g table is as simple as the following PL/SQL block:
INSERT INTO my_10g_table SELECT * FROM my_9i_table@9i_db_link;
The above PL/SQL block can be put into a stored procedure or can be put into the Scheduler to be run periodically. The "@9i_db_link" tells Oracle to access the table in the database denoted by the link. Please read the Oracle Administrators Guide and Concepts Guide (available at
) for information on setting up database links.
Dig Deeper on Using Oracle PL-SQL