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. 🙂