Re: Call NT batch file from Oracle stored procedure

From: Vimal <chakingal_at_hotmail.com>
Date: 19 Feb 2002 08:52:05 -0800
Message-ID: <f0dde875.0202190852.3a8e589f@posting.google.com>

Thanks! It worked....... had to do few changes ......though For people who are dumb and lazy like me and want proper sstep by step  instructions: This is what I did :
Ask the SYS user to give you following permissions : begin
dbms_java.grant_permission

    ('user_name', 'java.io.FilePermission', 'c:\path\*',
'execute');

dbms_java.grant_permission

   ('user_name', 'java.lang.RuntimePermission', 'c:\path\*',
'writeFileDescriptor' );

dbms_java.grant_permission
  ('user_name', 'java.lang.RuntimePermission', 'c:\path\*',
'readFileDescriptor' );

dbms_java.grant_permission
  ('user_name', 'java.io.FilePermission', 'c:\path\*',
'read,write');

end ;

Then, get the following roles :
grant JAVASYSPRIV to user_name ;
grant JAVAUSERPRIV TO user_name ;

I am not sure if you need this permission (but I got it) : dbms_java.grant_permission('user_name',

                                'java.util.PropertyPermission',
                                  '*',
                                 'read,write');

create or replace and compile java source named "Util" as
import java.io.* ;
import java.lang.* ;

public class Util extends Object
{

	public static int RunThis(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] ;
			
			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 ;
		}
	}

}         

• then write this function ---- create or replace function RUN_CMD( p_cmd in varchar2) return number as language java name 'Util.RunThis(java.lang.String[]) return integer';
• create proc ----- create or replace procedure sp_RC(p_cmd in varchar2) as x number; begin x := run_cmd(p_cmd); dbms_output.put_line('run_cmd returned : '|| rpad(x,3,' ') || ' for
  '|| p_cmd) ;
  end;

  ---

  For dbms_output to show... you will have to set serveroutput on size 1000000 exec dbms_java.set_output(1000000)

  ---

  When you pass parameter, be sure to pass full path name.

Thanks to all!!!!! I am glad it worked.....!!!

"Dieter Buecherl" <Dieter.Buecherl_at_t-online.de> wrote in message news:<a4qp4f$fkt$05$1_at_news.t-online.com>...

> check out:
> 
> http://asktom.oracle.com/pls/ask/f?p=4950:8:283708::NO::F4950_P8_DISPLAYID,F
> 4950_P8_CRITERIA:2043748131564,
> 
> Tom Kyte's example might point you in the right direction...
> 
> HTH Dieter
> 
> 
> ----- Original Message -----
> From: "Vimal" <chakingal_at_hotmail.com>
> Newsgroups: comp.databases.oracle.server
> Sent: Saturday, February 16, 2002 5:54 PM
> Subject: Call NT batch file from Oracle stored procedure
> 
> 
> > I am trying to call a batch file which will execute an exe created
> > using VB from a trigger created in Oracle. For e.g., I have a table
> > called job_staus which has a field 'process flag'. When the flag
> > become 'Y', I need to call the batch file which will in turn call the
> > exe.
> > Thanks,
> > Vimal.

Received on Tue Feb 19 2002 - 10:52:05 CST