[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3005
  • Last Modified:

HOW CAN I RUN A *.BAT FILE FROM DATABASE TRIGGER

Question:

I want to run a batch file via plsql trigger I have created it in the database. this batch file is opening an Oracle report. I have found this script to use java class to run the batch file through the command prompt.
I have recently run the following script but with no result:

1.
conn / as sysdba

2.
EXEC dbms_java.grant_permission('TMS', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC dbms_java.grant_permission('TMS', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
EXEC dbms_java.grant_permission('TMS', 'SYS:java.io.FilePermission', 'C:\\WINDOWS\\system32\\CMD.EXE', 'execute');

3.
connect tms/*****

4.
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
  public static void executeCommand(String command) {
    try {
      String[] finalCommand;
      if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1) {
        finalCommand = new String[4];
        finalCommand[0] = "C:\\WINDOWS\\system32\\cmd.exe";
        finalCommand[1] = "/y";
        finalCommand[2] = "/c";
        finalCommand[3] = command;
      } else {                                    // Linux or Unix System
        finalCommand = new String[3];
        finalCommand[0] = "/bin/sh";
        finalCommand[1] = "-c";
        finalCommand[2] = command;
      }
 
      // Execute the command...
      final Process pr = Runtime.getRuntime().exec(finalCommand);

      // Capture output from STDOUT...
      BufferedReader br_in = null;
      try {
        br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
        String buff = null;
        while ((buff = br_in.readLine()) != null) {
          System.out.println("stdout: " + buff);
          try {Thread.sleep(100); } catch(Exception e) {}
        }
        br_in.close();
      } catch (IOException ioe) {
        System.out.println("Error printing process output.");
        ioe.printStackTrace();
      } finally {
        try {
          br_in.close();
        } catch (Exception ex) {}
      }
 
      // Capture output from STDERR...
      BufferedReader br_err = null;
      try {
        br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
        String buff = null;
        while ((buff = br_err.readLine()) != null) {
          System.out.println("stderr: " + buff);
          try {Thread.sleep(100); } catch(Exception e) {}
        }
        br_err.close();
      } catch (IOException ioe) {
        System.out.println("Error printing execution errors.");
        ioe.printStackTrace();
      } finally {
        try {
          br_err.close();
        } catch (Exception ex) {}
      }
    }
    catch (Exception ex) {
      System.out.println(ex.getLocalizedMessage());
    }
  }

};
/
show errors

5.

CREATE OR REPLACE PROCEDURE host (p_command IN VARCHAR2)
   AS LANGUAGE JAVA
   NAME 'Host.executeCommand (java.lang.String)';
/
show errors

6.

CALL DBMS_JAVA.SET_OUTPUT(1000000);
SET SERVEROUTPUT ON SIZE 1000000
exec host('c:\SALES.BAT');

but after that there is no result.

please anyone can help me on this.


0
FAHADSSG
Asked:
FAHADSSG
  • 2
2 Solutions
 
oleggoldCommented:
You need to check whether Oracle instance on Your server has the appropriate permissions,genrally enough to check that Oracle dba group is also in the local admin group on Your windows machine
0
 
FAHADSSGAuthor Commented:
I checked that
I have a privilage of ORA_DBA in user groups priviliges.
0
 
FAHADSSGAuthor Commented:
---
0
 
kathir1978Commented:
Hi ,

Please help me out for the same thread. I am trying to execute one application when the new records gets into the table. I tried with the java code to run the batch file.
Pl/sql editor gets hanged when i execute  host_command('c:\test.bat').
but i could see, the application is running through task manager..  There is no output.

Please copy the solution here

thanks
kathir
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now