• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Copy data from Oracle Database to Access Database in a servlet

 
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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..
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Saloon Keeper
Posts: 27763
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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..
 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Will SQLLoader solve this problem? just wanted to clarify because i started to learn SQLLoader now.
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


also Is it possible to copy data from oracle database to access database via SQL Loader?


No.
 
Ranch Hand
Posts: 167
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hii
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.

Jhakda
 
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic