Home » SQL & PL/SQL » SQL & PL/SQL » Updating remote database
Updating remote database [message #276972] Sat, 27 October 2007 10:43 Go to next message
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 #276974 is a reply to message #276972] Sat, 27 October 2007 10:45 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
How about a Materialized View?

By
Vamsi
Re: Updating remote database [message #276975 is a reply to message #276974] Sat, 27 October 2007 11:18 Go to previous messageGo to next message
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 #276976 is a reply to message #276972] Sat, 27 October 2007 11:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>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.
HUH?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref844
Re: Updating remote database [message #276981 is a reply to message #276975] Sat, 27 October 2007 11:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: About Package
Next Topic: PLS_INTEGER
Goto Forum:
  


Current Time: Tue Feb 18 12:47:11 CST 2025