Hi i have got a scenario where i need to transfer the updated data from access to oracle database and also from oracle to access on click of a button.
My oracle and access database has got the same table structure , when application runs new data gets recorded first in access database and sets a reference flag to 'U' in all the tables now when i click the button 'upload to Oracle DB' i want a script to run such that the updated rows replaces the old rows present in oracle database.
I am not sure how to start with this i dont have much knowledge of oracle, can anybody suggets me how do i do this also is it something related to Sync the tables in both the databases, and how do i sync the tables present in two different databases. ELse is it something related to writing script which contains commands to copy tables to and fro and this script runs in the servlet where this servlet is called on clickof a button. Please help me out in this issue..
I have to ask, why is Access involved at all if you are ultimately persisting stuff in Oracle? What is the intermediate step for?
If you need an Access front end to an Oracle database (if for example you have a lot of forms developed in the Access database) you can use linking rather than replication. Access will allow you to create a table that is just a view of the Oralce data.
Here Why access database is required is... my application is used by survey people who works offline so when they are onsite on a survey my application stores the data into access database now when they are back from survey the data from access database is to be updated back into oracle database.
This is very much an Access model of working and very difficult to do with data sets of any complexity (you can't even do it successfully in Access, despite MS claims to the contrary).
What you have is a very long local cache that will be out of data as soon as it is used offline, and as a result a lot of lost data or conflicts when you try to merge the two. Synchronising databases in this way is very difficult and there is nothing in Oracle out the box to do this. To do this safely you'll really need to write something to interpret the Access transaction logs and tried to perform the same transactions on the Oracle database. No idea how you would do that.
Ok if i create views in access database for all the tables present in oracle database and update them offline (not connected to oracle database), now to update this data in oracle database can this be encorporated in some batch job? and how can this be done do i need a third party tool?
Take a look at the Pentaho Kettle utility. It's one of a number of ETL tools written in Java and it's very powerful. It can be launched as a stand-alone application or invoked on a scheule from the Pentaho BI server, and the Community Edition of Pentaho is free and open source..
An IDE is no substitute for an Intelligent Developer.
No. It is designed to load large quantities of data to a database that is (typically) offline. You need something that can manage online updates, inserts and deletes of data, and handle the failure behaviour when you can't insert, update or delete the data because its not valid anymore.
what i planned is the offline updates will go and sit in the access database , and when the user is online they run batch of sql loader from java and upload the rows with status as 'c' to oracle database. once the upload is done i will delete those rows that have status 'c' from access database, and again another batch to copy from oracle database to access database all rows that have status 'p'(pending). Is it approach efficient enough? also Is it possible to copy data from oracle database to access database via SQL Loader?
We had a similar problem. Used a job to synchronize the data between 2 databases, it read data from one, uploaded to the other. Make sure you address these issues that we faced
1. If 2 users updated the same data, how the conflict is resolved.
2. How do you manage sequence id of data inserted, as you say that the tables in both the DBs will be same.
3. Access is a bit unstable.
4. Perfect handshake is not possible here, say you select one record marked as "U" in access and try to upload to Oracle,if it succeeds, you have to mark it done in Access also. Say its updated in Oracle, but update fails in Access. Unless you follow"Check if exists in Oracle, if yes, update,else insert". But this will slow down your app.
5. How do you handle a situation where a few records got inserted, but others failed, do you roll back completely etc.
These are few issues i could remember.
If I become filthy rich, I'll sponsor research for painless dental treatment at Harvard Medical School. Thats why,I'm learning Java.I have 32 teeth, 22 are man made.
Third party apps like SQL Anywhere is supposed to handle much (though obviously not ALL) of the replication issues that you'll face, though I've never actually used it so I can't tell you how seamlessly it works.