Sorry if this question has come up before and has already been answered but I couldn't find any past threads that could help me.
I have an Oracle script that loads some of the columns of tables from one database, to the tables of another database.
Something to the effect of:
INSERT INTO DATABASE_A.TABLE1 ..., ..., ..., (SELECT ..., ..., ... FROM DATABASE_B.TABLE1);
INSERT INTO DATABASE_A.TABLE2 ..., ..., ..., (SELECT ..., ..., ... FROM DATABASE_B.TABLE2);
I am aware of the java Connection class methods; executeQuery(sqlString), executeUpdate(sqlString) etc. but these only seem to apply to single statements.
Is there any Class method that will allow this script to be run as a whole from it's directory or even as a String created from FileReader/BufferedReader?
I've been trying to work this in to my method but to no avail.
How do I actually structure the command String argument to the exec() method, given that my script is located at CATALINAHOME_HOME\webapps\projectName\scripts\oracleScript.sql
Sorry, that was a sutpid question, I know. I had been looking at it to long.
After a break I realised i just had to create a .bat file to connect to sqlplus and run the script from there, and then ran the batch file from Runtime.exec()
Maybe too late, but another valid approach could be to create a database link to database B and create a stored procedure to do the job. That way if an error occurs during the processing, you could at least get an exception. If you decide to go this path, I can help you with the links if you need.
I would appreciate those links you mentioned.
Although this is mainly for a project to demonstrate data warehouse aggregation, I would like an understanding of all possible approaches to this type of situation for future use.
Just looking at this thread again, you didn't mean links to web pages re the approach you suggested, did you? You meant actual help creating the link to database_B.
Very much appreciated but project time doesn't allow immediate research of alternatives.
But again, many thanks for the offer of help.
Yes, I meant database links. If you're interested, see the documentation. However, there would be quite some setup and design decisions to do if you were to use database links.
The solution based on database links would give you better control especially in case of error. In the script-based solution, you might use the WHENEVER SQLERROR sqlplus command to specify what should happen if an error occurs. There is also a similar WHENEVER OSERROR command (scroll up on the same page).
I'd put at least the WHENEVER SQLERROR EXIT ROLLBACK at the beginning of the script, so that the script rolls back if an error occurs in the middle.
I've included the WHENEVER SQLERROR clauses in all my scripts now and all seems to be working fine. The bulk of my work for the last few days has been the scripts to load the data warehouse dimension tables, checking for new rows and type 1 and type 2 attribute changes, and updating accordingly. Like you said, these were the least i should have included.