RE: Running scripts on Windows from Oracle stored procedures

From: John Dunn <jdunn_at_sefas.com>
Date: Thu, 26 Jan 2006 10:55:59 -0000
Message-ID: <000401c62267$185cab20$3e04050a@johnman>

Phil

Thanks for that.

I suspected that java was the way to go.

One complication I do have is that some of the script to be run are on servers other than the database server.

I presume I could use some form of rsh/ssh to run them...but do you know of a better option?

-----Original Message-----
From: Phil Jones [mailto:phillipjones_at_gmail.com] Sent: 26 January 2006 10:34
To: jdunn_at_sefas.com; oracle-l_at_freelists.org Subject: Re: Running scripts on Windows from Oracle stored procedures

Hi,

You can do this using a Java stored procedure. Don't have the time to write a full class for you, but you'll want to begin with something like this:

CREATE OR REPLACE JAVA SOURCE NAMED "execProg" AS import java.io.*;
public class execProg {
  public static void execute(String prog) {     try {

      String[] cmd = new String[2];
      cmd[0] = "c:\\windows\\system32\\cmd.exe";
      cmd[1] = prog;
      final Process proc = Runtime.getRuntime().exec(cmd);

   }
   catch (Exception e) {}
  }
};

.. then you'll need to add a grant to allow the user to execute cmd.exe:

BEGIN
dbms_java.grant_permission('USERNAME','java.io.FilePermission','c:\\windows\ \system32\\cmd.exe','execute');
END;
/

Hope this is a good starting point for you. (Note: Completely untested!!)

Cheers,

Phil

On 26/01/06, John Dunn <jdunn_at_sefas.com> wrote:
> I have a requirement to run some scripts on Windows from Oracle 9 stored
> procedures. The stored procedures are written in PL/SQL although Java is
an
> option.
> The scripts will be .bat files or perl or python programs.

>

> What is the best way to execute these from a stored procedure?. Oracle
> database platform on Windows will be Oracle 9 or 10.

>
>

> http://www.freelists.org/webpage/oracle-l

>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 26 2006 - 04:55:59 CST