Oracle: Executing an OS command from PL/SQL – ICMP ping from database

Executing OS commands from SQL or PL/SQL is easy as long as you are in SQL*PLUS – just do a host command. But I must confess, that this is a really useless hint. :) Much more interesting is to execute an OS command with a DBMS_SCHEDULER job, but this is not possible in every case, as it wasn’t in mine: I had to execute an ICMP ping and wait for the result to proceed, all that within a periodic DBMS_SCHEDULER job. No way to launch another job and wait until it’s started.

It took not much time to find out, that PL/SQL provides no native method to execute something on OS level. But the web was full of more or less useful hints what to do, the most useful one I found was from Ask Tom, a question about problems calling a in-database-java method for this case. The guy there used rt.exec() out of the database – nice, but Java and me …


That’s what I finally did to make my availibility check work:

First step is to create a Java Method that can do what I need, and that gives back the error code of the called command.

CREATE OR REPLACE and COMPILE JAVA SOURCE
NAMED "ExecuteOSCommand"
AS
import java.io.*;
import java.lang.*;
public class ExecuteOSCommand extends Object
{
  public static int RunThis(String args)
  {
    Runtime rt = Runtime.getRuntime();
    int        rc = -1;
    try
    {
       // System.out.println(args);
       Process p = rt.exec(args);

       int bufSize = 4096;
       BufferedInputStream bis =
        new BufferedInputStream(p.getInputStream(), bufSize);
       int len;
       byte buffer[] = new byte[bufSize];
       // Output of the program called
       while ((len = bis.read(buffer, 0, bufSize)) != -1)
            System.out.println(new String(buffer));
       rc = p.waitFor();
    }
    catch (Exception e)
    {
      e.printStackTrace();
      rc = -1;
    }
    finally
    {
      return rc;
    }
  }
}
/

Here we build a function that’s called later by our PL/SQL block:

create or replace function RUN_CMD( p_cmd  in varchar2)
return number
AS LANGUAGE JAVA
NAME  'ExecuteOSCommand.RunThis(java.lang.String) return integer';
/

For some cases, a procedure can be useful as well:

create or replace procedure CMD(p_cmd in varchar2)
as
  x number;
begin
  x := RUN_CMD(p_cmd);
end;
/

Now that’s bash, becoming /opt/oracle/bin/ping.sh, doing a ping to a host, and exiting with the error code of ping.

#!/bin/bash
/bin/ping -c5 -i0.5 $1 1> /dev/null
exit $?

As a least step, we need Java permissions to execute the script from the VM within the DB. Run this script from a DBA user’s account.

begin
  dbms_java.grant_permission
    (
    'SCOTT',
    'java.io.FilePermission',
    '/opt/oracle/bin/ping.sh',
    'execute'
    );
  dbms_java.grant_permission
    (
    'SCOTT',
    'java.lang.RuntimePermission',
    'writeFileDescriptor',
    ''
    );
  dbms_java.grant_permission
    (
    'SCOTT',
    'java.lang.RuntimePermission',
    'readFileDescriptor',
    ''
    );
end;
/

Finally, we are able to execute the ping.sh form RUN_CMD() in PL/SQL:

select run_cmd('/opt/oracle/bin/ping.sh ftp.example.com') result from dual;

The bash script has to be reachable and executable for the OS user/group that opens the PL/SQL session! This may be your web server, login user, whatever. If this is not the case, your error code may be 126 on Linux.

For debugging the whole bunch, to see java stack traces and so on, this snippet may be useful:

set serveroutput on size 1000000
exec dbms_java.set_output( 1000000 );
exec dbms_output.put_line( run_cmd('/usr/local/klugmon/ping.sh'));

Hope this helps, please be careful with execution rights, don’t use wildcards in the permission section.

Yours
Usn

PS: Thanks to Ask Tom, Mahomed for the approach, and Michael and Sebastian for finding the Java lice. :)




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Oracle: Executing an OS command from PL/SQL – ICMP ping from database”

  1. Andy Says:

    I have a trouble with executing OS commands from Oracle through java – process output stream not redirects in file.
    Oracle 10g + Java + Linux
    and same results in Windows XP.
    I’m trying with and without ‘bash -c’ in *nix and with ‘cmd /c’ in win, and with (“”) and (”) in different positions. But nothing is work.
    In other words:
    Commands like ‘ls -l’ or ‘tasklist’ is executing perfectly, until not used
    ‘ls -l > \oracle\java\dir.list’
    or
    ‘tasklist /v /nh > c:\temp\tasks.list’

    Can You do this (from Oracle)
    exec dbms_output.put_line( run_cmd(‘echo “IMAGE PID SESSION SID MEMORY STATUS USER CPUTIME HEADER” > “/oracle/java/ps.log”‘));

Leave a Reply