aspose file tools*
The moose likes JDBC and the fly likes Run Oracle Script from within JSP Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Run Oracle Script from within JSP" Watch "Run Oracle Script from within JSP" New topic
Author

Run Oracle Script from within JSP

Martin Kelly
Greenhorn

Joined: Jan 09, 2011
Posts: 12

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
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61180
    
  66

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.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Martin Kelly
Greenhorn

Joined: Jan 09, 2011
Posts: 12

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
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61180
    
  66

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30502
    
150

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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Martin Kelly
Greenhorn

Joined: Jan 09, 2011
Posts: 12

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
Greenhorn

Joined: Jan 09, 2011
Posts: 12

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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
Greenhorn

Joined: Jan 09, 2011
Posts: 12

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
Greenhorn

Joined: Jan 09, 2011
Posts: 12

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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
Greenhorn

Joined: Jan 09, 2011
Posts: 12

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.
 
Don't get me started about those stupid light bulbs.
 
subject: Run Oracle Script from within JSP