Q
Problem solve Get help with specific problems with your technologies, process and projects.

Stored procedure to auto-transfer data between servers

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?

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:
BEGIN
   INSERT INTO my_10g_table SELECT * FROM my_9i_table@9i_db_link;
END;
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 http://tahiti.oracle.com) for information on setting up database links.

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close