Re: Run Unix command

From: Ben de Boer <cbdeboer_at_planet.nl>
Date: Tue, 7 Feb 2006 13:41:28 +0100
Message-ID: <dsa4hq$buc4$1@news3.infoave.net>

But anyhow,
if you want to run something from a stored procedure, Create a java-source something like:
create or replace and compile java source named cmd_host as import java.lang.* ;
import java.io.* ;
/*
* ***********************************************************************

• @@Version : 1.1
• Author : BOERBDE/ CSC
• Creation date : 2002/08/06
• Purpose : Execute a host command
  *
  
• Usage :
• ***********************************************************************
  */
  

public class Host
{

private static String sys_path = "/usr/bin/";
private static String app_d_path = "/app/local/env1/bin/shell/";
private static String app_a_path = "/app/local/env2/bin/shell/";
private static String app_p_path = "/app/local/env3/bin/shell/";

public static String Exec (String command, String SysPth) throws Exception
{
String app_path = "";
String FullCmd = "";
String returnValue = "";
String S = "";
String DbName = "";
int Pos = 0;
try
{
Pos = 10;
/*
* Set the application path, depended of the database
*/

#sql {select sys_context('USERENV','DB_NAME') into :DbName from dual }; Pos = 20;
if (DbName.toUpperCase().equals("ENV1")) app_path = app_d_path;
if (DbName.toUpperCase().equals("ENV2")) app_path = app_a_path;
if (DbName.toUpperCase().equals("ENV3")) app_path = app_p_path;

Pos = 30;
Runtime r = Runtime.getRuntime();
/* For a proper execution of the commands
* the full path-name is needed.

• In this situation 2 paths are predefined
• and are substituted when SysPth = SYS or APP
• In other cases SysPth itself is used.
• Attention: End the path-name with /
  */
  Pos = 40; if (SysPth.toUpperCase().equals("SYS")) FullCmd = sys_path + command; else if (SysPth.toUpperCase().equals("APP")) FullCmd = app_path + command; else FullCmd = SysPth + command;

Pos = 50;
/*
* Execute the command
*/

Process p = r.exec(FullCmd);
Pos = 60;
/*
* Wait for return
*/

p.waitFor();

Pos = 70;
/*
* Read the result-buffer into the return-string
*/

BufferedReader in =
new BufferedReader(new InputStreamReader(p.getInputStream())); while((S=in.readLine()) != null)
{

// For debug
//System.out.println(S) ;
//

returnValue = returnValue + S+"\n";
}
Pos = 80;

/*
* Check for the return-code
*/

if (p.exitValue() != 0)
{
if (p.exitValue() == 255)
{
// In this specific case Java was not able to execute the command // So there will not be a return-string
returnValue = "Error :" + p.exitValue() +" Unable to execute\n"; }
else
{
returnValue = "Error :" + p.exitValue() + " execute\n" + returnValue; }
throw new IOException(""+ p.exitValue()); }
Pos = 90;
/*
* See if there was anything strange happened
*/

if (returnValue.length() > 0 )
{
if (returnValue.toUpperCase().indexOf("ACCESSCONTROLEXCEPTION") > 0) {
throw new IOException("5");
}
}
/*
* Now everything should be allright,

• so we can exit with 0
  */
  //returnValue = "Exit 0 Host.Exec @"+Pos+" ->\n" + returnValue; returnValue = returnValue + "->Exit 0 >>Host.Exec @"+Pos; } catch (IOException IOE) { //returnValue = "Exit 6 Host.Exec @"+Pos+" ->\n" + returnValue; returnValue = returnValue + "->Exit 6 >>Host.Exec @"+Pos; } catch (Exception e) { //returnValue = "Exit 6 Host.Exec @"+Pos+" ->\n" + e.toString() + " "+returnValue; returnValue = returnValue + "->Exit 6 >>Host.Exec @"+Pos; } return returnValue; } };

So, this is part 1, now create a function:

CREATE OR REPLACE FUNCTION CMD_HOST
(P_COMMAND IN VARCHAR2
,P_DIR IN varchar2
)
RETURN VARCHAR2
IS
LANGUAGE JAVA NAME 'Host.Exec(java.lang.String, java.lang.String) return java.lang.String';

This is working.

And why?

All control on this site is intended to come from the database, so if we want to FP a file, exported from Oracle to somewhere

it is initiated from the database.

Regards, Ben de Boer

"Jeremy" <jeremy0505_at_gmail.com> wrote in message news:MPG.1e527b1e6120790e98a0e4_at_news.individual.net...

> In article <pan.2006.01.26.13.03.14.28753_at_sbcglobal.net>, Mladen Gogala
> says...
> > On Thu, 26 Jan 2006 04:30:52 -0800, johnleslie wrote:
> >
> > > How can I run a unix command from a oracle 9i stored procedure?
> >
> > Why would you want to do that? There are several ways, one or two
> > were described in Tom Kyte's book (101), there was an external procedure
> > on Metalink, extproc_perl, Oracle 10g has DBMS_SCHEDULER package capable
> > of doing so, but the question still remains: why?
> >
> >
>
> I don't know the OP's needs but am wondering why you should consider it
> to be such an issue "why" he would want to run a unix command from
> inside the database? Perhaps he would like to copy some files or get a
> directory listing. Or maybe something complicated. In either case, I
> gather from your implied tone, that it is not a practice you are in
> favour of?
>
> -- 
>
> jeremy
>

Received on Tue Feb 07 2006 - 06:41:28 CST