29 September 2011

unx:Exception during creation of the process


I had created some automated test scripts for one of my projects. For utility functions, i had created a simple shell executor which is a java source and compiled in the oracle database in order to help me to execute unix commands from pl/sql.  After i did some tests, i realized that the shell executor results an error as shown below:

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as mennan

SQL> set serveroutput on;
SQL>
SQL> DECLARE
  2    CommandText  varchar2(4000);
  3  BEGIN
  4    CommandText := ShellExecutor(pis_command =>'/bin/ls -1tr');
  5    dbms_output.put_line( 'Return : ' ||  CommandText);
  6  END;
  7  /

Return : RET:unx:Exception during creation of the process

PL/SQL procedure successfully completed



 When i grant “java_deploy” role to the my user, the problem was solved:


SQL> connect sys/sysadm@testdb.vm as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as SYS

SQL> grant java_deploy to mennan;

Grant succeeded

SQL> connect mennan/sysadm@testdb.vm
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as mennan

SQL> set serveroutput on;
SQL>
SQL> DECLARE
  2    CommandText  varchar2(4000);
  3  BEGIN
  4    CommandText := ShellExecutor(pis_command =>'/bin/ls -1tr');
  5    dbms_output.put_line( 'Return : ' ||  CommandText);
  6  END;
  7  /

Return : RET:unx:init.ora
initdw.ora
lktestdb
hc_testdb.dat
orapwtestdb
spfiletestdb.ora

The source files of ShellExecutor is shown below:


Java source to be compiled in the database:
create or replace and compile java source named Shell as

import java.io.*;
public class Shell {
 
  public static String executeCommand(String _command) {
    String commandOutput, temp, inputStreamOutput = "", errorStreamOutput = "";

    try {
   
      String[] shellCommandArray;
      if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1) {
        shellCommandArray = new String[4];
        shellCommandArray[0] = "C:\\windows\\system32\\cmd.exe";
        shellCommandArray[1] = "/y";
        shellCommandArray[2] = "/c";
        shellCommandArray[3] = _command;
      }
      else {
        shellCommandArray = new String[3];
        shellCommandArray[0] = "/bin/sh";
        shellCommandArray[1] = "-c";
        shellCommandArray[2] = _command;
      }

      Process runtimeProcess = Runtime.getRuntime().exec(shellCommandArray);
      BufferedReader readerInputStream = new BufferedReader(new InputStreamReader(runtimeProcess.getInputStream()));
      temp = null;
      while ((temp = readerInputStream.readLine()) != null) {
      inputStreamOutput+=temp+ System.getProperty("line.separator") ;
      }
      readerInputStream.close();
     
      BufferedReader readerErrorStream = new BufferedReader(new InputStreamReader(runtimeProcess.getErrorStream()));
      temp = null;
      while ((temp = readerErrorStream.readLine()) != null) {
      errorStreamOutput+=temp + System.getProperty("line.separator") ;
      }
      readerErrorStream.close();

    }
    catch (Exception ex) {
    errorStreamOutput=ex.getMessage();
    }
   
    if ( errorStreamOutput.length() > 0  ){
    commandOutput = "E:"+errorStreamOutput;
    }else{
    commandOutput = "S:"+inputStreamOutput;
    }   
   
    return commandOutput;
   
     }

}


PL/,SQL Wrapper function:
CREATE OR REPLACE FUNCTION ShellExecutor(pis_CommandText IN VARCHAR2 )
  RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'Shell.executeCommand( java.lang.String ) return java.lang.String';
/



Sample test script:
set serveroutput on;


DECLARE
  vs_CommandText VARCHAR2(4000);
  vs_ReturnText  VARCHAR2(4000);
  vs_SuccessFail VARCHAR2(2);
BEGIN
  --To list directories
  --vs_CommandText := '/bin/ls -1tr';
 
  --To remove files
  vs_CommandText := '/bin/rm  /home/oracle/temp/dummy.txt';
 
  vs_ReturnText  := ShellExecutor(vs_CommandText);
  vs_SuccessFail := substr(vs_ReturnText, 1, 2);
  vs_ReturnText  := substr(vs_ReturnText, 3);

  IF vs_SuccessFail <> 'S:' THEN
    dbms_output.put_line('Failed!!!');
  END IF;

  dbms_output.put_line(vs_ReturnText);
END;
/

No comments: