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

Updating data in remote database when local database is updated

I do not know how to update the database remotely while I am entering data on another remote area of the database. In short, there two databases, one with Oracle 8i and one with Oracle 9i, at two different locations. At one location, one person enters data through the SQL form. The database at the other location, which is remote, should be automatically updated with the same record. Please give me details that I can understand easily.

The first thing you need to do is to create a database link pointing to the other location. The database link can...

be created with a command similar to the following:

CREATE DATABASE LINK other_db CONNECT TO scott IDENTIFIED BY tiger
USING 'tns_alias';

You'll want to give the database link a better name, use the appropriate userid/password to connect to the remote database, and configure your TNSNAMES.ORA file with a TNS alias to point to that database. You can test the database link with a simple query like the following:

SELECT sysdate FROM dual@other_db;

The "@other_db" clause uses the database link you created. If the query returns the date, then the link works properly. Once the link is set up, you can either issue a command to modify the data in the remote database when you modify the local database, or you can use a database trigger. For instance, if the application modifies the local database by performing an INSERT similar to the following,

INSERT INTO my_table VALUES (1,'Two');

then you can also code another SQL statement to insert the same values across a database link.

INSERT INTO my_table@other_db VALUES (1,'Two');

Additionally, you can create a trigger:

CREATE TRIGGER modify_remote_my_table
BEFORE INSERT ON my_table
BEGIN
  INSERT INTO my_table@other_db VALUES(:new.colA, :new.colB);
END;
/

You can write similar triggers for updating and deleting rows as well.

This was last published in November 2004

Dig Deeper on Oracle database backup and recovery

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close