wood burning stoves 2.0*
The moose likes Web Services and the fly likes Executing sqlplus or sqlldr from java code Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Web Services
Bookmark "Executing sqlplus or sqlldr from java code" Watch "Executing sqlplus or sqlldr from java code" New topic
Author

Executing sqlplus or sqlldr from java code

shah isha
Greenhorn

Joined: May 01, 2007
Posts: 18
hi,

I have my application on one server(tomcat) and oracle server is installed on other server. i.e. Both are on different machine.

Now i want to run sqlplus / sqlldr command on oracle server from my java code.

Again my script for lodder command is in temp of tomcat.

How to do that?

Thanks,
Peer Reynders
Bartender

Joined: Aug 19, 2005
Posts: 2922
    
    5
Use Runtime.getRuntime().exec(command) to run the command line versions of SQL*Plus and SQL*Loader provided that you have client-side Oracle installed on the Tomcat machine.

How to execute a command from code

If it is something that you do on a regular basis you may want to check if you can satisfy your requirements with some PL/SQL code on the DB server that you can then access via JDBC.

What does this have to do with web services?
shah isha
Greenhorn

Joined: May 01, 2007
Posts: 18
Thanks..

>>What does this have to do with web services?

I am using JDBC to execute queries...but i need to run sqlplus command to run script if some condition is satisfied and same for sqllodder. This code is in the thread and is working fine for same machine. Need to execute it on another machine where oracle is installed.

Oracle client is necessary on the mchine from where we are executing this command on Oracle server? Is there any other way?

From command prompt i am able to run sqlplus on Oracle server. (Oracle client is installed on this machine) Same command I am not able to run from the code.

Can you give me code. which can show how to connect to that machine. (Rightnow I have given access of bin directory of oracle to client machine) is it fine?? I am not satisfied with this solution.. How to give permission to that particular tomcat user??

Regards,

[ December 16, 2007: Message edited by: shah isha ]
[ December 17, 2007: Message edited by: shah isha ]
Peer Reynders
Bartender

Joined: Aug 19, 2005
Posts: 2922
    
    5
If you don�t have Oracle client on the server that runs the Tomcat servlet container then you can try and do the same thing on the DB server with a PL/SQL / Java Stored Procedure combination (which could be invoked by JDBC as a stored procedure) provided the scripts reside on the DB server (if they don't you can use PL/SQL with the utl_file package to help create the scripts on the DB server).

Examples: here and here

In Oracle 10 you can use the built-in dbms_scheduler package instead.

However this may take some determination to get it to work because the OS security settings have to be just right to allow the process to complete.

See: here

Still doesn't have anything to do with web services...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Executing sqlplus or sqlldr from java code