Home » Infrastructure » Linux » Unable to execute shell script from sql
Unable to execute shell script from sql [message #225639] Tue, 20 March 2007 12:19 Go to next message
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 Go to previous messageGo to next message
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 #229615 is a reply to message #225650] Mon, 09 April 2007 06:59 Go to previous messageGo to next message
timarcher52
Messages: 5
Registered: April 2007
Junior Member
Hello. I tried to go the java route a long time ago, but never got it working. In any case, I have a simple writeup on how to run host commands from pl/sql using a C based external procedure. Its extremely simple to do.
My writeup is at:
http://timarcher.com/?q=node/9
Re: Unable to execute shell script from sql [message #229658 is a reply to message #229615] Mon, 09 April 2007 08:56 Go to previous message
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 ...
Previous Topic: How to Configue Microsoft Access Odbc In Linux Using ODBCConfig
Next Topic: Crontab problem
Goto Forum:
  


Current Time: Thu Mar 28 15:38:51 CDT 2024