13 Replies Latest reply: Mar 14, 2011 3:48 AM by 327282 RSS

    Java running host command - moved from PL/SQL forums

    HG75
      Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
      PL/SQL Release 11.1.0.6.0 - Production
      CORE 11.1.0.6.0 Production
      TNS for HPUX: Version 11.1.0.6.0 - Production
      NLSRTL Version 11.1.0.6.0 - Production

      Hi,
      I am not familiar with Java setting in Oracle
      I have a a stored procedure that call a java program to execute host command like
      ssh user1@localhost '/home/user1/someprogram'

      if we execute this directly using PuTTY

      we can do the following with no problem

      su - oracle
      ssh user1@localhost '/home/user1/someprogram'

      but when we execute the stored procedure,
      we have to do

      */usr/bin/ssh* user1@localhost '/home/user1/someprogram'

      does anybody where to set the path or environment to make the java know the path correctly.

      thanks


      ps:. this is happen after we recently upgraded our Oracle from 9.2.0.8 to 11.1.0.6
        • 1. Re: Java running host command - moved from PL/SQL forums
          12cdb
          You said,
          a stored procedure that call a java program to execute host command like ssh user1@localhost '/home/user1/someprogram'
          SP -calls> Java Program (JP) -runs> ssh user1@localhost '/home/user1/someprogram'. is that right? I presume locahost is a database server.

          Where does Java Progarm reside? On the database server filesystem OR within the Database itslef.

          If JP resides on the file system, then SP, should make use of UTL_FILE package (more details can be found at http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/u_file.htm) and should be able to run the JP program which will eventually run ssh command.

          It becomes complicated if JP itself is a Java Stored Procedure resides within the database, the SP should call Java Stored Procedure, which eventually calls the same UTL_FILE package and run the command.

          HTH
          • 2. Re: Java running host command - moved from PL/SQL forums
            sb92075
            /usr/bin/ssh user1@localhost '/home/user1/someprogram'
            does anybody where to set the path or environment to make the java know the path correctly.
            you can gain some visibility by doing as below just as a test

            /usr/bin/ssh user1@localhost 'env | sort -o /tmp/capture.env'

            The OS environment does not contain "/usr/bin" as part of PATH variable.
            specific answer depends upon which is the default shell.
            • 3. Re: Java running host command - moved from PL/SQL forums
              HG75
              pgoel wrote:
              You said,
              a stored procedure that call a java program to execute host command like ssh user1@localhost '/home/user1/someprogram'
              SP -calls> Java Program (JP) -runs> ssh user1@localhost '/home/user1/someprogram'. is that right? I presume locahost is a database server.
              correct

              this is the stored procedure
              CREATE OR REPLACE PROCEDURE host_command (p_command  IN  VARCHAR2)
                AS LANGUAGE JAVA
                NAME 'Host.executeCommand (java.lang.String)';
              and this is the java
              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 (isWindows()) {
                      finalCommand = new String[4];
                      // Use the appropriate path for your windows version.
                      finalCommand[0] = "C:\\windows\\system32\\cmd.exe";  // Windows XP/2003
                      //finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";  // Windows NT/2000
                      finalCommand[1] = "/y";
                      finalCommand[2] = "/c";
                      finalCommand[3] = command;
                    }
                    else {
                      finalCommand = new String[3];
                      finalCommand[0] = "/bin/sh";
                      finalCommand[1] = "-c";
                      finalCommand[2] = command;
                    }
              
                    final Process pr = Runtime.getRuntime().exec(finalCommand);
                    pr.waitFor();
              
                    new Thread(new Runnable(){
                      public void run() {
                        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("Process out :" + buff);
                            try {Thread.sleep(100); } catch(Exception e) {}
                          }
                          br_in.close();
                        }
                        catch (IOException ioe) {
                          System.out.println("Exception caught printing process output.");
                          ioe.printStackTrace();
                        }
                        finally {
                          try {
                            br_in.close();
                          } catch (Exception ex) {}
                        }
                      }
                    }).start();
              
                    new Thread(new Runnable(){
                      public void run() {
                        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("Process err :" + buff);
                            try {Thread.sleep(100); } catch(Exception e) {}
                          }
                          br_err.close();
                        }
                        catch (IOException ioe) {
                          System.out.println("Exception caught printing process error.");
                          ioe.printStackTrace();
                        }
                        finally {
                          try {
                            br_err.close();
                          } catch (Exception ex) {}
                        }
                      }
                    }).start();
                  }
                  catch (Exception ex) {
                    System.out.println(ex.getLocalizedMessage());
                  }
                }
              
                public static boolean isWindows() {
                  if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
                    return true;
                  else
                    return false;
                }
              
              };
              Where does Java Progarm reside? On the database server filesystem OR within the Database itslef.
              within the Database itslef.

              Edited by: HGDBA on Mar 11, 2011 1:50 PM
              • 4. Re: Java running host command - moved from PL/SQL forums
                HG75
                oracle@CSD-HPUX/home/oracle#echo $PATH
                /usr/bin:/opt/ansic/bin:/usr/ccs/bin:/usr/contrib/bin:/usr/contrib/Q4/bin:/opt/p                                                                             erl/bin:/opt/ipf/bin:/opt/hparray/bin:/opt/nettladm/bin:/opt/fcms/bin:/opt/sec_m                                                                             gmt/bastille/bin:/opt/resmon/bin:/opt/gnome/bin:/usr/bin/X11:/usr/contrib/kwdb/b                                                                             in:/opt/wbem/bin:/opt/wbem/sbin:/opt/graphics/common/bin:/opt/sec_mgmt/spc/bin:/                                                                             opt/hpsmh/bin:/opt/upgrade/bin:/usr/contrib/bin/X11:/oracle11g/ora11g/bin:/opt/o                                                                             rdat/bin64:/opt/acucobol701/bin:/opt/langtools/bin:/opt/hpnpl/bin:/opt/ignite/bi                                                                             n:/opt/perf/bin:/opt/omni/bin:/opt/omni/lbin:/opt/omni/sbin:/opt/prm/bin:/opt/mp                                                                             ux/bin:/usr/local/bin:/opt/java1.4/bin:/home/root/scripts:/opt/caliper/bin:/opt/                                                                             cadvise/bin:/opt/cfg2html:/opt/drd/bin
                oracle@CSD-HPUX/home/oracle#
                seems like /usr/bin is set in the PATH for the OS environment.
                Is the java cannot use the same PATH?
                • 5. Re: Java running host command - moved from PL/SQL forums
                  sb92075
                  seems like /usr/bin is set in the PATH for the OS environment.
                  For interactive session, this is what I would expect.
                  Is the java cannot use the same PATH?
                  I expect Java going out to the OS/shell would behave much like a cron job that does NOT by default source in any environment.

                  You can see for yourself EXACTLY what Java has by doing my previously posted test using env | sort
                  • 6. Re: Java running host command - moved from PL/SQL forums
                    12cdb
                    why do not you use one procedure from the following

                    public Process exec(String command, String[] envp) throws IOException (TRY THIS)

                    public Process exec(String command, String[] envp, File dir) throws IOException

                    envp - array of strings, each element of which has environment variable settings in the format name=value, or null if the subprocess should inherit the environment of the current process

                    set all JavaHome, Classpath in envp and supply it.

                    Edited by: pgoel on Mar 11, 2011 7:14 PM

                    Edited by: pgoel on Mar 11, 2011 7:14 PM
                    • 7. Re: Java running host command - moved from PL/SQL forums
                      HG75
                      Hi we tried not to alter anything since it worked fine in our old 9.2.0.8

                      last resort will be to give full path, however developer prefers if we have any environment setting to accomodate this.
                      • 8. Re: Java running host command - moved from PL/SQL forums
                        sb92075
                        Hi we tried not to alter anything since it worked fine in our old 9.2.0.8
                        The issue with with new OS configuration; not application code.
                        • 9. Re: Java running host command - moved from PL/SQL forums
                          HG75
                          in 9i
                          I executed from the java
                          ssh WebDrug@localhost 'env | sort -o /tmp/capture1.env'

                          and the result is
                          oracle@CST-HPUX/tmp#cat capture1.env
                          HOME=/home/WebDrug
                          LOGNAME=WebDrug
                          LOG_SFTP=1
                          MAIL=/var/mail/WebDrug
                          PATH=/usr/bin:/bin:/usr/sbin:/sbin:/opt/ssh/bin
                          PWD=/home/WebDrug
                          SFTP_LOG_FACILITY=3
                          SFTP_LOG_LEVEL=3
                          SFTP_PERMIT_CHMOD=1
                          SFTP_PERMIT_CHOWN=1
                          SFTP_UMASK=22
                          SHELL=/usr/bin/sh
                          SSH_CLIENT=127.0.0.1 52299 22
                          SSH_CONNECTION=127.0.0.1 52299 127.0.0.1 22
                          TZ=EST5EDT
                          USER=WebDrug
                          _=/usr/bin/env
                          oracle@CST-HPUX/tmp#
                          in 11g
                          if I executed
                          ssh WebDrug@localhost 'env | sort -o /tmp/capture.env'
                          no result.....

                          if I executed
                          /usr/bin/ssh WebDrug@localhost 'env | sort -o /tmp/capture.env'
                          result is
                          oracle@CSD-HPUX/tmp#cat capture.env
                          HOME=/home/WebDrug
                          LOGNAME=WebDrug
                          LOG_SFTP=1
                          MAIL=/var/mail/WebDrug
                          PATH=/usr/bin:/bin:/usr/sbin:/sbin:/opt/ssh/bin
                          PWD=/home/WebDrug
                          SFTP_LOG_FACILITY=3
                          SFTP_LOG_LEVEL=3
                          SFTP_PERMIT_CHMOD=1
                          SFTP_PERMIT_CHOWN=1
                          SFTP_UMASK=22
                          SHELL=/usr/bin/sh
                          SSH_CLIENT=127.0.0.1 58616 22
                          SSH_CONNECTION=127.0.0.1 58616 127.0.0.1 22
                          TZ=EST5EDT
                          USER=WebDrug
                          _=/usr/bin/env
                          oracle@CSD-HPUX/tmp#
                          • 10. Re: Java running host command - moved from PL/SQL forums
                            12cdb
                            user environment variable which you have pasted is correct but unforutnate they are not being used by JAVA,

                            can you test the procedure independently... if it is working..


                            SQL> exec host('/usr/bin/ls');
                            • 11. Re: Java running host command - moved from PL/SQL forums
                              12cdb
                              I do not think there is an issue with the OS, its only his code, where code is not setting the corrent environments before it is being run. They run in a separate thread and requires all environment settings to be done explicitly for that thread. application is not doing it, which I do not understand why?

                              I doubt if the code was running in his old environment? if yes, OP should run the command what I said in my last reply and paste the output.
                              • 12. Re: Java running host command - moved from PL/SQL forums
                                12cdb
                                I do not think there is an issue with the OS, its only his code, where code is not setting the corrent environments before it is being run. They run in a separate thread and requires all environment settings to be done explicitly for that thread. application is not doing it, which I do not understand why?

                                I doubt if the code was running in his old environment? if yes, OP should run the command what I said in my last reply and paste the output.
                                • 13. Re: Java running host command - moved from PL/SQL forums
                                  327282
                                  Hi

                                  This is a classic example of how hard-coded values within programs can affect portability. I think you could recommend the following to the developer(s):

                                  1. Move all variable environment settings to a properties file (values stored as key-value pairs) which will be read by the java program during runtime. It's easy to change the values according to the envionment (windows, Unix etc)
                                  (or)
                                  2. Change the program such that the values can be passed as arguments.

                                  I think the above changes (the first one is more robust) are fairly simple to code and implementing one of the above would make the program truly portable. It's worth spending the effort in my opinion.

                                  Regards