I'm CaLendarW Blog

Create Trigger to Run Jar using Shell in Oracle 10g

by calendarw on Mar.14, 2008, under Uncategorized

First login to sys and grant the permission in sqlplus

EXEC dbms_java.grant_permission('ABC', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC dbms_java.grant_permission('ABC', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
EXEC dbms_java.grant_permission('ABC', 'SYS:java.io.FilePermission', '/bin/sh', 'execute');

And then, login to ABC and create java in sqlplus:

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:\\winnt\\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());
    }
  }
};

after that, create procedure in sqlplus:

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

set the output display if neccessary in sqlplus:

CALL DBMS_JAVA.SET_OUTPUT(1000000);
SET SERVEROUTPUT ON

You can test the procedure using following command:

exec host('/bin/ls');

Create a sh file in linux:

#!/bin/bash
source /home/abc/.bash_profile
cd /home/abc
java -jar /home/abc/abc.jar

Remember to change mode in linux:

chmod a+x /home/abc/run.sh

Finally, create the trigger:

create or replace trigger abc_run
after update on abc.testing
begin host('/home/abc/run.sh');
end;
commit;

Reference: Ref1 Ref2

:

Leave a Reply

You must be logged in to post a comment.

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!