I have a set of tables for which the data needs to copied to another environment.
Here are the key points:
1. I do not want to copy the entire data, it will be driven by a set of instructions from screen and related master and detail tables (around 20 in number) we should be able to migrate to another environment.
2. We can safely assume its plain insert and there will not be any violation of constraints
3. This process should be done seamlessly as its repetitive process (done from screen ref point 1) , via a stored procedure or something better - experts i need your suggesstion here
4. There will not be any provision for DB Links, ideally because we want to demonstrate there is no overhead except the same table structure should reside in the environments.
5. Dont mind if its takes time in loading the data, but it should ideally be done in background when user initiates action from screen and may be reads config files to find target db, without invoking command prompt or any process that can be called from java.
This activity is called Integration/Interfacing one application with other.
Flow can be like this
1) 1st environment application, in the form / sreen, 1 group window need to be available.
That group consists of those 20 tables list. And individual or group click option should be available. By clicking on this, an export of these tables to .CSV should be performed based on the partial selection of data. For this, in the code, Oracle SQL commands can be used to generated the .CSV output.
2) .CSV file should be generated in a folder which is NFS mounted(this is a network mount in unix systems to avoid FTP. But it has its own security/technical related good and bad. Consult System Admin)
So with this, no need to separately transfer the files to 2nd environment. Seamless.
3) 2nd Environment application Screen/Form. 1 group window or individuval table window should be available along with import button. When clicked, in the background, it should use sqlloader or insert commands to load the data. This should be handled in the code.
Note : Formatting of the .CSV file should also be handled to import. It need to be aligned properly so that import will be successfully completed.
Scope of work : Developer/Sql programmer,Application Owner who understands the application and is useful to developer to complete this interfacing.
Clue : In Oracle ERP environment, this approach is popular with concurrent request
Assuming you have already considered it have you discounted it or are you just looking for possible alternatives?