Executing Host Commands Using JAVA Stored Procs

From: billiauk <billiauk_at_yahoo.co.uk>
Date: 30 Jan 2002 05:00:07 -0800
Message-ID: <dee17a9f.0201300500.e5a5807@posting.google.com>

I have compiled some code that I downloaded to execute host commands from PL/SQL using JAVA stored procedures. Everything compiles fine and the host commands I execute are working EXCEPT that the procedure doesn't return. I am testing on Personal 8.1.6 Win98 with a view to adding to Solaris later.

I verified this by creating a file E:\temp\ade.bat batch script that copies e:\temp\output.log to e:\temp\output.log2

Now when I execute this procedure, I can see the new file appearing in the Windows Explorer, so I know the ade.bat file is executing. I've also tested the ade.bat file independently to ensure uit exits. However, my SQL*Plus session is hanging indefinitely and is not returning at all. Can anyone help.

Code below.

TIA Adrian

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "HostCmd" AS
import java.io.*;
import java.lang.*;

public class HostCmd extends Object
{

  public static int ExecCmd(String[] args)
{

  Runtime rt = Runtime.getRuntime();
  int rc = -1;

  try
{

     Process p = rt.exec(args[0]);

     int bufSize = 4096;
     BufferedInputStream bis = new
BufferedInputStream(p.getInputStream(), bufSize);
     int len;
     byte buffer[] = new byte[bufSize];

     // Echo back what the program spits out
     while ((len = bis.read(buffer, 0, bufSize)) != -1)
        System.out.write(buffer, 0, len);

     rc = p.waitFor();

  }
  catch (Exception e)
{

     e.printStackTrace();
     rc = -1;

  }
  finally
{

     return rc;
  }
  }
}
/

CREATE OR REPLACE FUNCTION f_host_cmd (

                           cmd_in IN VARCHAR2
                           ) RETURN NUMBER AS 

LANGUAGE JAVA
NAME 'HostCmd.ExecCmd(java.lang.String[]) return integer'; /

CREATE OR REPLACE PROCEDURE p_host_cmd (

                            Command_in IN VARCHAR2
                            ) AS

   v_RC NUMBER;
BEGIN
   v_RC := f_host_cmd(Command_in);
END;
/

set serveroutput on size 1000000
exec dbms_java.set_output(1000000)

grant javasyspriv to billia;

exec dbms_java.grant_permission('BILLIA',
                                'java.io.FilePermission',
                                'ALL FILES',
                                'execute');

exec p_host_cmd('e:\temp\ade.bat'); Received on Wed Jan 30 2002 - 07:00:07 CST