Forums Register Login

Run Oracle Script from within JSP

+Pie Number of slices to send: Send
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:
Begin
INSERT INTO DATABASE_A.TABLE1 ..., ..., ..., (SELECT ..., ..., ... FROM DATABASE_B.TABLE1);
INSERT INTO DATABASE_A.TABLE2 ..., ..., ..., (SELECT ..., ..., ... FROM DATABASE_B.TABLE2);
--etc.
Commit;
End;
/

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?

Many thanks in advance.
+Pie Number of slices to send: Send
This is not something that should be done in a JSP. Modern JSP should have no Java code within them -- let along DB access code.

In any case, this has been moved tot he JDBC forum.
+Pie Number of slices to send: Send
Of course they shouldn't. I should have been clearer; I am trying to do this from a java class method that is executed based on a list selection made on the JSP.
+Pie Number of slices to send: Send
Cool. Then we are definitely in the right forum now.

As far as I know, JDBC has no means to run script files. Perhaps there's a 3rd party or open source solution?
+Pie Number of slices to send: Send
JDBC does not have this capability. I use Runtime.exec() to run a database script.
+Pie Number of slices to send: Send
Thanks Jeanne.
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
+Pie Number of slices to send: Send
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()
Job done.
Thanks.
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
Thanks Martin,
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.
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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.
Many thanks.
Fire me boy! Cool, soothing, shameless self promotion:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 3247 times.
Similar Threads
Comparing Dates in MS Access
JDBC start/stop database needed
how to check no of connection
Importing data into a database with a complex schema using Java
help!! ResultSet error
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 19, 2024 03:26:53.