asked on

Invoking UNIX shell command (mailx) from Oracle Java stored procedure

The program below is suppose do send an email using UNIX mailx program. It works correctly when I compile it in UNIX and invoke it from the command line by sending an email to the given address.

I need this program to run as a stored procedure, however. I deploy it as such and try to invoke it. It prints the results correctly to the standard output. It does not send any emails, however. One other difference in execution is that when invoked from the command line, the program takes about a minute to return. When invoked as a stored procedure in PL/SQL program or SQL*Plus anonymous block, it returns immediately.

Why would mailx invocation not work from a stored procedure? Are there other ways to invoke mailx from PL/SQL?

Here is how the Java stored procedure is invoked from SQL*Plus:

declare
mailx_result number;
begin
dbms_output.enable(100000);
dbms_java.set_output(100000);
dbms_java.grant_permission( user, 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' );
dbms_output.put_line(to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS') || ' started acting_cron');
mailx_result := java_utilities.mailx('Hey there', 'Hi', 'oracle@solaris10ora', 1);
end;

Thank you.

Michael

Program code:

import java.io.BufferedWriter;
import java.io.IOException;
import java.io.OutputStreamWriter;

public class MailUtility
{
public static void main(String[] args)
{
System.out.println(mailx("Hey, there", "Hello", "oracle@solaris10ora", 1));
}

/**
* Sends a message using UNIX mailx command.
* @param message message contents
* @param subject message subject
* @param addressee message addressee
* @param display if greater than 0, display the command
* @return OS process return code
*/
public static int mailx(String message, String subject,
String addressee, int display)
{
System.out.println("In mailx()");

try
{
String command =
"echo \"" + message + "\" | mailx -r a@b.c" + " -s \"" + subject + "\" " + addressee;
if (display > 0)
{
System.out.println(command);
}

try
{
Process process = Runtime.getRuntime().exec("/bin/bash");
BufferedWriter outCommand =
new BufferedWriter(new OutputStreamWriter(process.getOutputStream()));
outCommand.write(command, 0, command.length());
outCommand.newLine();
outCommand.write("exit", 0, 4);
outCommand.newLine();
outCommand.flush();
process.waitFor();
outCommand.close();
return process.exitValue();
}
catch (IOException e)
{
e.printStackTrace();
return -1;
}

}
catch (Exception e)
{
e.printStackTrace();
return -1;
}
}
}

ASKER CERTIFIED SOLUTION

sventhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sventhan

The above code I got it from metalink Note:306106.1.

Please let me know if you need any sample.

msmolyak

ASKER

Thank you for you post, sventhan. Where do I find the Java source for ExecOSCmdLog.javaand CaptureStream.java?

Michael

SOLUTION

konektor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sventhan

ExecOSCmdLog.java
-------------------code begins here---------------------------------------------

import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class ExecOSCmdLog {
public static void doit(String args[])
{
boolean STDOUT = false;
boolean STDERR = false;
boolean LOGGER = true;
InputStream so = null;
InputStream se = null;
Thread o =null;
Thread e =null;

int rc = -1;
String cmd = "Error inproper call";
FileOutputStream fos = null;
if (args.length < 1) {
System.out.println("USAGE: java ExecOSCmdLog \'cmd\' ");
System.exit(1);
}

try {
cmd = args[0];

if (args.length >1 ) {
for (int i =1, j = args.length ; i < j; i++) {
if (args[i].equals("STDOUT")) { // out and log
STDOUT= true;
} else if (args[i].equals("STDERR")) { // out err and log
STDERR= true;
STDOUT= true;
} else if (args[i].equals("NOLOG")) { // out and no log
LOGGER= false;
STDOUT= true;
} else if (args[i].equals("NONE")) {
// eat the defaults
// nothing to the screen, do logging
} else {
fos = new FileOutputStream(args[1]);
STDOUT= true;
}
}
}
Runtime rt = Runtime.getRuntime();
Process p = rt.exec(cmd);
so = p.getInputStream();
se = p.getErrorStream();

if (STDOUT) {
capture(so, fos, "OUT ",o);
}
if (STDERR) {
capture(se, null, "ERR ",e);
}

try {
rc = p.waitFor();
/* Handle exceptions for waitFor() */
} catch (InterruptedException intexc) {
System.out.println("Interrupted Exception on waitFor: " +
intexc.getMessage());
}

if ( o != null) {
o.join();
o = null;
}
if ( e != null) {
e.join();
e = null;
}
if (fos !=null) {
fos.flush();
fos.close();
}
se.close();
so.close();
System.out.println("ExitValue: " + rc);

} catch (Throwable t) {
System.out.println("ExitValue: " + rc);
t.printStackTrace();
} finally {
if (LOGGER) {
LogCmd(cmd,rc);
STDOUT = false;
STDERR = false;
LOGGER = true;
}
}

}

private static void capture( InputStream is,
FileOutputStream fos,
String label,
Thread o ){
CaptureStream out = new CaptureStream(is, label, fos );
o = new Thread(out);
o.start();
}

private static void LogCmd(String cmd, int rc){
try {
final String insert =
"INSERT INTO OS_CMD_LOG (CALLER,CMD,RETURN_VALUE)"+
" VALUES ( SYS_CONTEXT('USERENV', 'SESSION_USER' ), "+
" '"+ cmd + "', "+ rc + " ) " ;
// As the connection should not be closed anyway
Statement stmt =
DriverManager.getConnection
("jdbc:default:connection:").createStatement();
stmt.executeUpdate(insert);
stmt.close();

} catch (SQLException e ) {
e.printStackTrace();
}
}

}

}

-----------------code ends here-------------------------------------------------
and the supporting class

CaptureStream.java
-------------------code begins here---------------------------------------------
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.PrintWriter;

class CaptureStream implements Runnable {
private final InputStream is;
private final String label;
private final OutputStream redirect;
private boolean redirected = false;

CaptureStream(InputStream is, String label, OutputStream redirect)
{
this.is = is;
this.label = label + "> ";
this.redirect = redirect;
}

CaptureStream(InputStream is, String label)
{
this(is, label, null);
}

CaptureStream(InputStream is)
{
this(is, " ", null);
}

public void run()
{

try {
PrintWriter pw = null;
if (redirect != null) {
pw = new PrintWriter(redirect);
redirected = true;
}

InputStreamReader isr = new InputStreamReader(is);
BufferedReader br = new BufferedReader(isr);
String line=null;
while ( (line = br.readLine()) != null) {
System.out.println(label + line);
if (redirected) {
pw.println(line);
}
}
if (redirected) {
pw.flush();
pw.close();
}
br.close();
isr.close();
} catch (IOException ioe) {
ioe.printStackTrace();
}
}
}

-----------------code ends here-------------------------------------------------