Updating remote database [message #276972] |
Sat, 27 October 2007 10:43  |
solweb
Messages: 3 Registered: October 2007 Location: UK
|
Junior Member |
|
|
Hi,
I am looking at the best ways of updating records in an Oracle 9i database which will automatically update records in another Oracle 9i database and I wonder if anyone has experience of doing this and the method they took.
If there are many records and the update doesn't have to be real time, I thought perhaps running SQL Loader in a nightly batch job, or creating a flat file and using UTL_FILE to read the file and update each record were 2 possible methods.
If it's real time, then I thought about Remote Procedure Calls or triggers or even using COPY command could be methods although there could be network issues.
Just wondering if anyone has done this and a few pointers would be a great help.
Thanks
Solweb.
|
|
|
|
Re: Updating remote database [message #276975 is a reply to message #276974] |
Sat, 27 October 2007 11:18   |
solweb
Messages: 3 Registered: October 2007 Location: UK
|
Junior Member |
|
|
Hi,
Thanks.
I did wonder about a materilized view but that's just coping then into a view rather than the table itself.
i.e. if I have table EMP for example in my remote database and I want to update it with the records from EMP in my master database,then if I use a materialized view then I am coping the records from the master database to the view.
I would then have to write a procedure to take them from the view into my EMP table, that's why I dismissed this method.
Thanks.
Solweb.
|
|
|
|
Re: Updating remote database [message #276981 is a reply to message #276975] |
Sat, 27 October 2007 11:52   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
If you created a MV you don't have to write any procedure, the MV is automatically updated.
You should read documentation about MV. You seem to know too less on the subject to be able to fully understand the answers we can give you.
[Sorry Ana, didn't read your answer before posting.]
Regards
Michel
[Updated on: Sat, 27 October 2007 11:53] Report message to a moderator
|
|
|
Re: Updating remote database [message #276989 is a reply to message #276972] |
Sat, 27 October 2007 13:00   |
solweb
Messages: 3 Registered: October 2007 Location: UK
|
Junior Member |
|
|
Thanks guys.
I will read up more on materialized views.
Not sure if it will be the solution though for the following reasons (and I should have put this in my original post) ...
1) I already have records in my remote EMP table that I want to keep. These records were taken from the master EMP table originally, but I needed to then update some of these on the remote database only (therefore some columns store different values between the master and remote database). If I do a REFRESH on the remote site - surely I will then overwrite the data on the remote site with the current data from the master site
2) As I said, I already have some records in my remote site that I want to keep. If I create an MV, then what I will have on my remote site is a table EMP and an MV EMP as opposed to just an updated table EMP.
Hope this is understandable.
Thanks
Solweb.
|
|
|
Re: Updating remote database [message #276990 is a reply to message #276989] |
Sat, 27 October 2007 13:25  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
You have to give more details in your specification.
Actually there is no master and secondary but 2 softly dependent tables.
The implementation depends on the precise requirements on what is before, what is now, what is propagated, what is not, how and so on.
Regards
Michel
|
|
|