| Unable to execute shell script from sql [message #225639] |
Tue, 20 March 2007 12:19  |
samyak_m
Messages: 5
Registered: November 2005
Location: New Delhi
|
Junior Member |
|
|
Hi all,
I created a java class in oracle 10g database ...
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1) {
finalCommand = new String[4];
finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
} else { // Linux or Unix System
finalCommand = new String[3];
finalCommand[0] = "/usr/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}
// Execute the command...
final Process pr = Runtime.getRuntime().exec(finalCommand);
// Capture output from STDOUT...
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("stdout: " + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
} catch (IOException ioe) {
System.out.println("Error printing process output.");
ioe.printStackTrace();
} finally {
try {
br_in.close();
} catch (Exception ex) {}
}
// Capture output from STDERR...
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("stderr: " + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
} catch (IOException ioe) {
System.out.println("Error printing execution errors.");
ioe.printStackTrace();
} finally {
try {
br_err.close();
} catch (Exception ex) {}
}
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}
};
/
Then i created the procedure host as
CREATE OR REPLACE PROCEDURE host (p_command IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
After that i gave all required file permissions for pl/sql ...
EXEC dbms_java.grant_permission('USER', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC dbms_java.grant_permission('USER', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
EXEC dbms_java.grant_permission('USER', 'SYS:java.io.FilePermission', '/usr/bin/sh', 'execute');
but when i try a simple command in sqlprompt like :-
SQL> exec host('cat /opt/oracle/test/result.txt > /opt/oracle/test/result.txt ');
stderr: /usr/bin/sh: line 1: cat: No such file or directory
PL/SQL procedure successfully completed.
This gives error as follows ... all other file permissions are present ....and oracle user has full rights ...
Can you suggest please because the same procedure of host is working when Database is on Windows ..... But currently the Database Oracle 10g Release 2 is installed on SLES 9 ....
Thanks in Advance ,
Samyak ...
|
|
|
|
| Re: Unable to execute shell script from sql [message #225650 is a reply to message #225639] |
Tue, 20 March 2007 13:45  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Most likely the process which is trying to run your command has NO environment variables & maybe not even any shell.
determine where your favorite resides with fully qualified pathname
For example:
which bash
/usr/local/bin/bash
then as a test do via Java
"/usr/local/bin/bash /bin/env > /tmp/test.env"
If you only ever use fully qualified pathnames for everything, you should have no problems.
[Updated on: Tue, 20 March 2007 14:43] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Unable to execute shell script from sql [message #229658 is a reply to message #229615] |
Mon, 09 April 2007 08:56 |
samyak_m
Messages: 5
Registered: November 2005
Location: New Delhi
|
Junior Member |
|
|
Hello Tim ,
Your article on this topic of running host command from PL/SQL is great ... i was looking for an alternative way of running host commands without using java and this seems just like the one that can achieve my purpose ... Thanks indeed for the solution ...
Samyak ...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
