Run Oracle Script from within JSP

[Martin Kelly]

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.

[Bear Bibeault]

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.

[Martin Kelly]

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.

[Bear Bibeault]

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?

[Jeanne Boyarsky]

JDBC does not have this capability. I use Runtime.exec() to run a database script.

[Martin Kelly]

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

[Martin Kelly]

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.

[Martin Vashko]

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.

[Martin Kelly]

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.

[Martin Kelly]

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.

[Martin Vashko]

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.

[Martin Kelly]

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.