• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Run Oracle Script from within JSP

 
Greenhorn
Posts: 12
Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 12
Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
JDBC does not have this capability. I use Runtime.exec() to run a database script.
 
Martin Kelly
Greenhorn
Posts: 12
Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 12
Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 12
Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 12
Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 12
Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic