11 Replies Latest reply: Jan 6, 2012 1:52 PM by user1093647 RSS

    Java stored procedure to execute shell script

    704192
      Hi,
      I have trouble executing a java stored procedure which inturn executes a shell script on the database server. Oracle is installed on HP-UX
      My java stored procedure executes fine when run from sqlplus. But I have a problem calling the java stored proc from inside an oracle stored procedure. I encounter java.io.IOException: Exception during creation of the process for the Runtime.exec call.

      My Java Stored proc is
      create or replace PROCEDURE executecmd (S1 VARCHAR2)
      AS LANGUAGE JAVA
      name 'ExecuteCmd.main(java.lang.String[])';

      The java code is:
      import java.lang.Runtime;
      import java.lang.Process;
      import java.io.*;
      import java.lang.InterruptedException;

      class ExecuteCmd {

      public static void main(String args[]) {

      System.out.println("In main");

      try {
      /* Execute the command using the Runtime object and get the
      Process which controls this command */

      Process p = Runtime.getRuntime().exec(args[0]);

      /* Use the following code to wait for the process to finish
      and check the return code from the process */
      InputStream shellIn = null;
      try {
      shellIn = p.getInputStream();
      p.waitFor();

      /* Handle exceptions for waitFor() */

      } catch (InterruptedException intexc) {

      System.out.println("Interrupted Exception on waitFor: " +
      intexc.getMessage());
      }

      System.out.println("Return code from process"+ p.exitValue());

      int c;

      while ((c = shellIn.read()) != -1) {System.out.write(c);}
      // close the stream
      try {
      shellIn.close();
      }
      catch (IOException ignoreMe) {
      System.out.println("IO Exception closing stream : "+ignoreMe);
      }
      System.out.println("Done executing");

      /* Handle the exceptions for exec() */

      } catch (IOException e) {
      System.out.println("IO Exception from exec : " +
      e.getMessage());
      e.printStackTrace();
      }
      catch (Exception ex)
      {
      System.out.println("Exception from exec : " +
      ex.getMessage());
      ex.printStackTrace();
      }
      }
      }

      My oracle stored proc is
      create or replace PROCEDURE P_call_upload_file
      (
      i_aud_type IN VARCHAR,
      i_fin_year IN VARCHAR,
      i_upld_type IN VARCHAR,
      i_login_ref IN VARCHAR,
      i_bank_pk IN NUMBER
      )
      AS
      l_bchfile VARCHAR(200) := '/TEST/UPLOAD/test.sh';
      l_command VARCHAR(1000) := l_bchfile || ' ' || i_aud_type || ' ' || i_fin_year || ' ' || i_upld_type || ' ' || i_login_ref;

      BEGIN
      insert into msg_tbl values('trying to execute script@ ' || sysdate);
      insert into msg_tbl values(l_command);
      executecmd(l_command);
      insert into msg_tbl values('completed execution@ ' || sysdate);
      commit;
      END;

      When I execute the wrapped java stored proc executecmd, I get the following results
      SQL> EXEC executecmd('/TEST/UPLOAD/test.sh B_AUDIT 2008-2009 ALLOC 984569/5103/28052009060306');
      In main
      Return code from process0
      Executing send mail procedure
      SQL*Plus: Release 11.1.0.6.0 - Production on Fri May 29 17:02:31 2009
      Copyright (c) 1982, 2007, Oracle. All rights reserved.
      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      Done executing

      PL/SQL procedure successfully completed.
      No issues running the java stored proc directly

      When I execute the oracle stored procedure I encounter IOException
      SQL> EXEC P_call_upload_file('B_AUDIT', '2008-2009', 'ALLOC', '984569/5103/28052009060306',
      NULL);
      In main
      IO Exception from exec : Exception during creation of the process
      java.io.IOException: Exception during creation of the process
      at java.lang.OracleProcess.start(OracleProcess.java:146)
      at java.lang.ProcessBuilder.start(ProcessBuilder.java:483)
      at java.lang.Runtime.exec(Runtime.java:591)
      at java.lang.Runtime.exec(Runtime.java:429)
      at java.lang.Runtime.exec(Runtime.java:326)
      at ExecuteCmd.main(ExecuteCmd:16)
      PL/SQL procedure successfully completed.

      I have granted following permissions
      call dbms_java.grant_permission('TEST', 'java.io.FilePermission','<<ALL FILES>>', 'read ,write, execute, delete');

      call Dbms_Java.Grant_Permission('TEST', 'java.lang.RuntimePermission', 'writeFileDescriptor', '');

      call Dbms_Java.Grant_Permission('TEST', 'java.lang.RuntimePermission', 'readFileDescriptor', '');

      call Dbms_Java.Grant_Permission('TEST', 'java.net.SocketPermission', '*', 'connect,resolve');


      Any help in this regard would be appreciated.
        • 1. Re: Java stored procedure to execute shell script
          brtk
          Hi,

          I have no idea why your code doesn't work, but...

          2 hour ago I also had a similar problem with executing OS command from java and sample classes described in MOS 306106.1 helped me much.
          Those classes works called from pl/sql procedure (at least in my case).


          Bartek
          • 2. Re: Java stored procedure to execute shell script
            704192
            I am stuck with this problem for past 3 weeks. The proc and script run absolutely fine on Windows with oracle installed. I have trouble running it with HP-UX and encounter this error.
            Can you pls point to MOS 306106.1 link.. I could not find it.
            • 3. Re: Java stored procedure to execute shell script
              brtk
              MOS means My Oracle Support aka metalink.
              And the link is [http://metalink.oracle.com].
              Then put the document number in search field.

              To read mos documents you need to have a valid mos account.

              Bartek
              • 4. Re: Java stored procedure to execute shell script
                user11219185
                Hi
                I have a java stored procedure which executes a shell script. It works fine in our development environment and returns the exit code of 0. The same code doesn't seem to work fine in our production environment. The exit code returned is 2 which means misuse of shell builtin in bash programming. Any idea what might be the problem.

                Thanks
                • 5. Re: Java stored procedure to execute shell script
                  brtk
                  You didn't provide the code called from Java. How should we know what was the reason of error 2?
                  If the code is able to be called from java from test database, then you must check what is the difference between test and production.
                  Paths? Libraries? OS privilieges?

                  Bartek
                  • 6. Re: Java stored procedure to execute shell script
                    user11219185
                    Here's my Java Stored Proc

                    CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "ExecuteOSCmdLog" as import java.awt.*;
                    import java.applet.*;
                    import java.lang.*;
                    import java.io.*;
                    import java.sql.*;

                    public class ExecOSCmdLog {
                    public static void doit(String args[])
                    {
                    boolean STDOUT = false;
                    boolean STDERR = false;
                    boolean LOGGER = true;
                    InputStream so = null;
                    InputStream se = null;
                    Thread o =null;
                    Thread e =null;

                    int rc = -1;
                    String cmd = "Error inproper call";
                    String log = "Logging started";
                    FileOutputStream fos = null;
                    if (args.length < 1) {
                    System.out.println("USAGE: java ExecOSCmdLog \'cmd\' ");
                    System.exit(1);
                    }

                    try {
                    cmd = args[0];

                    if (args.length >1 ) {
                    for (int i =1, j = args.length ; i < j; i++) {
                    if (args.equals("STDOUT")) { // out and log
                    STDOUT= true;
                    } else if (args[i].equals("STDERR")) { // out err and log
                    STDERR= true;
                    STDOUT= true;
                    } else if (args[i].equals("NOLOG")) { // out and no log
                    LOGGER= false;
                    STDOUT= true;
                    } else if (args[i].equals("NONE")) {
                    // eat the defaults
                    // nothing to the screen, do logging
                    } else {
                    fos = new FileOutputStream(args[1]);
                    STDOUT= true;
                    }
                    }
                    }
                    Runtime rt = Runtime.getRuntime();
                    Process p = rt.exec(cmd);
                    so = p.getInputStream();
                    se = p.getErrorStream();

                    if (STDOUT) {
                    capture(so, fos, "OUT ",o);
                    }
                    if (STDERR) {
                    capture(se, null, "ERR ",e);
                    }

                    try {
                    rc = p.waitFor();
                    /* Handle exceptions for waitFor() */
                    } catch (InterruptedException intexc) {
                    System.out.println("Interrupted Exception on waitFor: " +
                    intexc.getMessage());
                    }


                    if ( o != null) {
                    o.join();
                    o = null;
                    }
                    if ( e != null) {
                    e.join();
                    e = null;
                    }
                    if (fos !=null) {
                    fos.flush();
                    fos.close();
                    }
                    se.close();
                    so.close();
                    System.out.println("ExitValue: " + rc);


                    } catch (Throwable t) {
                    System.out.println("ExitValue: " + rc);
                    t.printStackTrace();
                    } finally {
                    if (LOGGER) {
                    LogCmd(cmd,log,rc);
                    STDOUT = false;
                    STDERR = false;
                    LOGGER = true;
                    }
                    }

                    }

                    private static void capture( InputStream is,
                    FileOutputStream fos,
                    String label,
                    Thread o ){
                    CaptureStream out = new CaptureStream(is, label, fos );
                    o = new Thread(out);
                    o.start();
                    }


                    private static void LogCmd(String cmd,String log, int rc){
                    try {
                    final String insert =
                    "INSERT INTO OS_CMD_LOG (CALLER,CMD,RETURN_VALUE)"+
                    " VALUES ( SYS_CONTEXT('USERENV', 'SESSION_USER' ), "+
                    " '"+ cmd + "', "+ rc + " ) " ;
                    // As the connection should not be closed anyway
                    Statement stmt =
                    DriverManager.getConnection
                    ("jdbc:default:connection:").createStatement();
                    stmt.executeUpdate(insert);
                    stmt.close();
                    final String insert1 =
                    "INSERT INTO JOB_EXEC_LOGS (ITEM_ID,LOG_LEVEL,CHANGE_DATE,MESSAGE,LINE_NUM)"+
                    " VALUES ( 2431 ,"+ " 0 ,"+ " sysdate, "+ " SYS_CONTEXT('USERENV', 'SESSION_USER' ) ||"+
                    " ' OS Command: ' ||" + " '" + cmd + "',"+ "job_log_seq.NEXTVAL" + " ) " ;
                    // As the connection should not be closed anyway
                    Statement stmt1 =
                    DriverManager.getConnection
                    ("jdbc:default:connection:").createStatement();
                    stmt1.executeUpdate(insert1);
                    stmt1.close();
                    } catch (SQLException e ) {
                    e.printStackTrace();
                    }
                    }


                    }
                    /

                    Hope you can help.
                    • 7. Re: Java stored procedure to execute shell script
                      brtk
                      CALLING Java procedure seems to be ok. But in last post you complained about exit code in CALLED script.

                      Where the exit code (=2) is generated? If in a script, then provide a script. In Java? Well, I can't see the place where it could be.

                      Bartek
                      • 8. Re: Java stored procedure to execute shell script
                        Shane K
                        Hi,
                        I just received the same error (Exception during creation of the process). Resolved this by granting the JAVA_DEPLOY role to the user who was attempting to run the JSP. Hope this helps someone.

                        Shane
                        • 9. Re: Java stored procedure to execute shell script
                          514498
                          Hi.

                          I have a problem with the stored procedure at a time to execute for call a shell script on UNIX (HP-UX) attach the procedure and the shell script for help. When execute return the number 2. My oracle database is version 10.2.0.4 10gR2 on Oracle RAC running over HP-UX(11.23)

                          ----THIS IS THE PROCEDURE-------
                          SQL> set serveroutput on
                          SQL> exec DBMS_JAVA.SET_OUTPUT(1000000)

                          PL/SQL procedure successfully completed.

                          SQL> DECLARE
                          2 vl_declare NUMBER(5);
                          3 BEGIN
                          4 vl_declare := K_SEG_UTILITARIOS.F_RUN_CMD ('/usr/bin/sh /u03/EnlaceBCH/Utilitarios/ generar.cmd GPG PA N');
                          5 DBMS_OUTPUT.put_line ('Return:'||nvl(vl_declare,'0'));
                          6 EXCEPTION
                          7 WHEN OTHERS
                          8 THEN
                          9 DBMS_OUTPUT.put_line (SQLERRM);
                          10 END;
                          11 /
                          Return:2

                          PL/SQL procedure successfully completed.


                          ---THIS IS THE SHELL SCRIPT-----
                          #!/usr/bin/sh
                          #Variables a Utilizar#
                          #--------------------#
                          export pa_tipo_archivo=$1 #Tipo: TXT o GPG
                          export pa_tipo_lote=$2 #Tipos de Lote: PA,VC,OD,TC,EX
                          export pa_operacion=$3 #Tipo Operacion: N(No Juntar), S(Añadir)
                          export pa_archivo=/u03/SIAFI_DOCENTES/pruebas/listado_archivos_siafi.lst
                          export pa_dir_bch=/u03/SIAFI_DOCENTES/pruebas
                          export pa_dir_sefin=/u03/SIAFI_DOCENTES/pruebas
                          export pa_txt=TXT
                          export pa_gpg=GPG
                          #export pa_dir_bch=/u03/home/bch/home/bch/Home_BCH
                          #export pa_dir_sefin=/u03/EnlanceBCH/Home_BCH

                          #Cuerpo Pricipal del Shell#
                          #-------------------------#
                          #se valida el tipo de archivo si es GPG o TXT
                          if [ $pa_tipo_archivo = "GPG" ];then
                          #se valida el tipo de operacion
                          if [ $pa_operacion = "N" ];then
                          #Se valida el tipo de lote
                          if [ $pa_tipo_lote = "PA" ];then     
                               rm /usr/bin/$pa_archivo
                          #Se cambia al directorio
                               #cd /usr/bin/$pa_dir_bch/TRB
                               #Se listan los GPGP
                               for i in $(/usr/bin/ls *.gpg);do
                               echo $pa_tipo_archivo","$i","$pa_tipo_lote >> $pa_archivo
                               done     
                               elif [ $pa_tipo_lote = "VC" ];then
                               rm /usr/bin/$pa_archivo
                               #Se cambia al directorio
                               #cd /usr/bin/$pa_dir_bch/VALIDACION
                               #Se listan los GPGP
                               for i in $(/usr/bin/ls *.gpg);do
                               echo $pa_tipo_archivo","$i","$pa_tipo_lote >> $pa_archivo
                               done
                               elif [ $pa_tipo_lote = "OD" ];then
                               rm /usr/bin/$pa_archivo
                          #Se cambia al directorio
                               #cd /usr/bin/$pa_dir_bch/OSDE
                               #Se listan los GPGP
                               for i in $(/usr/bin/ls *.gpg);do
                               echo $pa_tipo_archivo","$i","$pa_tipo_lote >> $pa_archivo
                               done
                               elif [ $pa_tipo_lote = "TC" ];then
                               rm /usr/bin/$pa_archivo
                          #Se cambia al directorio
                               #cd $pa_dir_bch/TEC
                               #Se listan los GPGP
                               for i in $(/usr/bin/ls *.gpg);do
                               echo $pa_tipo_archivo","$i","$pa_tipo_lote >> $pa_archivo
                               done
                               elif [ $pa_tipo_lote = "EX" ];then
                               rm /usr/bin/$pa_archivo
                          #Se cambia al directorio
                               #cd $pa_dir_bch/RECFIS
                               #Se listan los GPG
                               for i in $(/usr/bin/ls *.gpg);do
                               echo $pa_tipo_archivo","$i","$pa_tipo_lote >> $pa_archivo
                               done
                          fi
                          else
                          #se pasa al directorio de TXT
                          cd /usr/bin/$pa_dir_sefin

                          #se listan los archivos txt
                          for i in $(/usr/bin/ls *.txt);do
                               echo $pa_txt","$i",">>$pa_archivo
                               done
                          fi
                          else
                          if [ $pa_operacion = "S" ];then
                          #Se valida el tipo de operacion
                                    if [ $pa_tipo_lote = "PA" ];then     
                                    #Se cambia al directorio
                                    #cd $pa_dir_bch/TRB
                                    #Se listan los GPGP
                                    for i in $(/usr/bin/ls *.gpg);do
                                         echo $pa_gpg","$i","$pa_tipo_lote >> $pa_archivo
                                    done     
                                    elif [ $pa_tipo_lote = "VC" ];then
                                    #Se cambia al directorio
                                    #cd $pa_dir_bch/VALIDACION
                                    #Se listan los GPGP
                                    for i in $(/usr/bin/ls *.gpg);do
                                         echo $pa_gpg","$i","$pa_tipo_lote >> $pa_archivo
                                    done
                                    elif [ $pa_tipo_lote = "OD" ];then
                                    #Se cambia al directorio
                                    #cd $pa_dir_bch/OSDE
                                    #Se listan los GPGP
                                    for i in $(/usr/bin/ls *.gpg);do
                                         echo $pa_gpg","$i","$pa_tipo_lote >> $pa_archivo
                                    done
                                    elif [ $pa_tipo_lote = "TC" ];then
                                    #Se cambia al directorio
                                    #cd $pa_dir_bch/TEC
                                    #Se listan los GPGP
                                    for i in $(/usr/bin/ls *.gpg);do
                                         echo $pa_gpg","$i","$pa_tipo_lote >> $pa_archivo
                                    done
                                    elif [ $pa_tipo_lote = "EX" ];then
                                    #Se cambia al directorio
                                    #cd $pa_dir_bch/RECFIS
                                    #Se listan los GPG
                                    for i in $(/usr/bin/ls *.gpg);do
                                         echo $pa_gpg","$i","$pa_tipo_lote >> $pa_archivo
                                    done
                                    fi
                          else
                          #se borra archivo
                               if [ -f $pa_archivo ];then
                               /usr/bin/rm $pa_archivo
                               fi
                               
                          #se listan los archivos txt
                          for i in $(/usr/bin/ls *.txt);do
                               echo $pa_tipo_archivo","$i",">>$pa_archivo
                               done
                          fi
                          fi

                          If you could tell me where is the error, I will apriacete.

                          Thanks....
                          • 10. Re: Java stored procedure to execute shell script
                            824700
                            Hi all,

                            I have a similar problem.

                            In windows environment I can call a batch file from an oracle stored procedure (loaded java class).
                            However, the same setup does not work on unix - on unix I am using shell scripts with the same logic as in my windows batch. The procedure is called successfully, but the shell script is not running... I can run my shell script directly from the unix command line successfully, so the script is ok.

                            Could anyone, please, advice on what could be the difference in calling shell script in unix vs. batch in windows from Oracle Stored Procedures? Could it be that the Oracle user in UNIX box should have some privileges granted to run shell scripts?

                            Thanks in advance,
                            • 11. Re: Java stored procedure to execute shell script
                              user1093647
                              I used the java program at the top of the thread and it was working great for some commands like ls or cat, but it kept returning 255 if i tried to run a script or print. It turned out when the process was opened there is no PATH set. So, i simply preceeded the command with ksh (ksh /app/oracle/testj.sh) and it works great now.