Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6100
  • Last Modified:

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;
}
}
}
0
msmolyak
Asked:
msmolyak
  • 3
2 Solutions
 
sventhanCommented:
Execution Environment:
sql*plus
loadjava
dropjava

Access Privileges:
CREATE PROCEDURE.

Usage:
Inside sqlplus:

execute redirectcmd(<command> [,<output-file>] ) ;

Instructions:

Here is a step by step example of how to create a JSP that makes an operating
system call. This sample was created and tested on Solaris 2.8, using Oracle
9.2.0 (but it can be run in 8i or 9i), with JDK 1.3.1, among others. A JDK is
not required to use this note.



A. Create the java source file that is located in the script section.

B. Load the .class file into the database.
$> javac ExecOSCmdLog.java
$> javac CaptureStream.java
$> loadjava -u scott/tiger -g PUBLIC ExecOSCmdLog.class
$> loadjava -u scott/tiger -g PUBLIC CaptureStream.class

NOTE: If you have already loaded a class of this name before, drop the
previous java or class before re-loading it:

$> dropjava -u scott/tiger ExecuteCmd.java
$> dropjava -u scott/tiger ExecuteCmd.class

C. Publish the Java code to PL/SQL by creating the PL/SQL wrapper.

----------------code begins here------------------------------------------------
create.sql

CREATE OR REPLACE PROCEDURE executecmd (cmd VARCHAR2,
redirect VARCHAR2,
log VARCHAR2 )
AS LANGUAGE JAVA
name 'ExecOSCmdLog.doit(java.lang.String[])';
/

-- wrapper as DEFAULTs cannot be done
CREATE OR REPLACE PROCEDURE OSCmd( cmd VARCHAR2,
redirect VARCHAR2 default 'NONE',
log VARCHAR2 default 'NONE' ) as
BEGIN
executecmd( cmd, redirect, log ) ;
END;
/
-- Also create a version to capture the output to a file

CREATE OR REPLACE PROCEDURE redirectcmd (cmd VARCHAR2, file VARCHAR2 )
AS LANGUAGE JAVA
name 'ExecOSCmdLog.doit(java.lang.String[])';
/
-- Create the logging table
CREATE TABLE os_cmd_log( caller VARCHAR2( 32 ),
cmd VARCHAR2( 255 ),
return_value NUMBER,
when_completed TIMESTAMP DEFAULT SYSTIMESTAMP )
/
GRANT EXECUTE ON executecmd TO PUBLIC
/
GRANT EXECUTE ON redirectcmd TO PUBLIC
/
GRANT EXECUTE ON OSCmd TO PUBLIC


----------------code ends here--------------------------------------------------
Also SYS may need to grant various Java Permissions, such as
exec dbms_java.grant_permission('SCOTT','SYS:java.io.FilePermission', '<<ALL FILES>','execute');
exec dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'readFileDescriptor', '*' )
exec dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '*' )
0
 
sventhanCommented:
The above code I got it from metalink Note:306106.1.

Please let me know if you need any sample.
0
 
msmolyakAuthor Commented:
Thank you for you post, sventhan. Where do I find the Java source for ExecOSCmdLog.javaand CaptureStream.java?

Michael
0
 
konektorCommented:
you can use utl_mail, or utl_tcp to send mails from PL/SQL without using additional java code
0
 
sventhanCommented:
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-------------------------------------------------
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now