Why my java stored procedure does not execute the command on HP/UNIX os ? Please help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Why my java stored procedure does not execute the command on HP/UNIX os ? Please help

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Posts
    18

    Question

    I have created a java SP on oracle 8i to launch the excutable file . This SP works fine on WINDOWS NT/2000 plateform.
    and when I execute this SP from the client machine it does the
    the things which I wanted it, to do it.

    When I created the SP on Oracle 8i running on HP/UX and pass the command line from sqlplus running on client machine(Windows 2000) it does nothing? I am puzzeled !!!! Why it does not execute the command on UNIX os any clue.


    I am doing the following to create the SP's.


    [1] Java program

    Save this program in runj.java file.


    import java.lang.*;
    import java.io.*;

    public class runj{
    public static void runme(String strexe,String strdjs) {
    Runtime rt = Runtime.getRuntime();
    String[] callAndArgs = { strexe, strdjs };
    try {
    Process child = rt.exec(callAndArgs);
    child.waitFor();
    //System.out.println("Process exit code is: " + child.exitValue());
    }
    catch(IOException e) {
    //System.err.println( "IOException starting process!");
    }
    catch(InterruptedException e)
    {//System.err.println( "Interrupted waiting for process!");
    }
    }
    }

    [2] Compile this program using the following command

    loadjava -verbose -resolve -schema scott -user scott/tiger runj.java


    Note: run this command from the directory where runj.java file resides.
    Do not use system as schema in the above command it will give u an error.




    [3] Create the following SP from the sqlplus

    CREATE OR REPLACE PROCEDURE RUNEXE(str1 VARCHAR2,str2 VARCHAR2)
    AS
    LANGUAGE JAVA
    NAME 'runj.runme(java.lang.String,java.lang.String)';


    [4] Execute the SP RUNEXE from sqlplus as follows

    execute runexe('exp Username/password tables=tablename',' ');

    Here exp is oracle Export utility replace the schema name and table name according to your database set up.

    Note : I AM USING MY DBA USER ID AND PASSWORD IN EACH AND EVERY STEP NOT THE SCOTT AND TIGER.

    Please tell me that it works....

    Thanks




  2. #2
    Join Date
    Apr 2002
    Location
    Chennai
    Posts
    27
    Originally posted by ashish23
    I have created a java SP on oracle 8i to launch the excutable file . This SP works fine on WINDOWS NT/2000 plateform.
    and when I execute this SP from the client machine it does the
    the things which I wanted it, to do it.

    When I created the SP on Oracle 8i running on HP/UX and pass the command line from sqlplus running on client machine(Windows 2000) it does nothing? I am puzzeled !!!! Why it does not execute the command on UNIX os any clue.


    I am doing the following to create the SP's.


    [1] Java program

    Save this program in runj.java file.


    import java.lang.*;
    import java.io.*;

    public class runj{
    public static void runme(String strexe,String strdjs) {
    Runtime rt = Runtime.getRuntime();
    String[] callAndArgs = { strexe, strdjs };
    try {
    Process child = rt.exec(callAndArgs);
    child.waitFor();
    //System.out.println("Process exit code is: " + child.exitValue());
    }
    catch(IOException e) {
    //System.err.println( "IOException starting process!");
    }
    catch(InterruptedException e)
    {//System.err.println( "Interrupted waiting for process!");
    }
    }
    }

    [2] Compile this program using the following command

    loadjava -verbose -resolve -schema scott -user scott/tiger runj.java


    Note: run this command from the directory where runj.java file resides.
    Do not use system as schema in the above command it will give u an error.




    [3] Create the following SP from the sqlplus

    CREATE OR REPLACE PROCEDURE RUNEXE(str1 VARCHAR2,str2 VARCHAR2)
    AS
    LANGUAGE JAVA
    NAME 'runj.runme(java.lang.String,java.lang.String)';


    [4] Execute the SP RUNEXE from sqlplus as follows

    execute runexe('exp Username/password tables=tablename',' ');

    Here exp is oracle Export utility replace the schema name and table name according to your database set up.

    Note : I AM USING MY DBA USER ID AND PASSWORD IN EACH AND EVERY STEP NOT THE SCOTT AND TIGER.

    Please tell me that it works....

    Thanks



    Please mention your Oralce Version using on the Unix Environment. If you are using the Oracle 8.1.6 on Unix, there is a problem in the Oracle Server to Initialize the Runtime. You need to switch to either Oracle 8.1.5 or Oracle 8.1.6 for any Unix Platform and then try to execute, then U shall be successful in executing this program.

    -Srinivas.

  3. #3
    Join Date
    Mar 2002
    Posts
    18
    Oracle version is 8.1.7 and HP/UNIX11.
    I run the program from UNIX prompt it gave me the return code 17 .This code is returned by the following command.
    child.exitValue() in my java code

  4. #4
    Join Date
    Mar 2002
    Posts
    18

    Smile Success!!!! Thank you everybody who helped me in devloping this java SP ..

    here is the final code which works from HP/UX Oracle8i and
    Winodws 2000-client.


    ---------------javac ExecuteCmd.java---------------------------
    import java.lang.Runtime;
    import java.lang.Process;
    import java.io.IOException;
    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 */
    try {

    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());

    System.out.println("Done executing");

    /* Handle the exceptions for exec() */

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

    B. Compile the Java source file.

    $> javac ExecuteCmd.java

    (This creates a file called ExecuteCmd.class)


    C. Load the .class file into the database.

    $> loadjava -u scott/tiger ExecuteCmd.class

    NOTE:
    If you have already loaded a class of this name before,
    drop the class before re-loading it, or use the -f (force) option:

    $> dropjava -u scott/tiger ExecuteCmd.class
    $> loadjava -u scott/tiger ExecuteCmd.class -f

    D. Publish the Java code to PL/SQL by creating the PL/SQL wrapper.

    ----------------code begins
    here------------------------------------------------
    -- create.sql
    CREATE OR REPLACE PROCEDURE executecmd (S1 VARCHAR2)
    AS LANGUAGE JAVA
    name 'ExecuteCmd.main(java.lang.String[])';
    /
    ----------------code ends
    here--------------------------------------------------

    E. Call the procedure.

    Example 1
    ---------

    SQL> set serveroutput on
    SQL> call dbms_java.set_output(2000);

    SQL> EXEC executecmd('/usr/bin/ls -la');

    In main
    Return code from process 0
    Done executing

    PL/SQL procedure successfully completed.

    Note:
    A return code of zero was returned. System call completed succesfully.

    Example 2
    ---------

    SQL> EXEC executecmd('ls -la');
    In main
    Return code from process 255
    Done executing

    PL/SQL procedure successfully completed.

    Note:
    A return code of 255 was returned. System call did not complete. Full
    paths
    must be specified for the OS command and any file reference.

    Example 3
    ---------

    1. Create a file called test.txt in your local directory.

    SQL> !vi test.txt
    SQL>!ls
    test.txt

    2. Call the Java stored procedure.

    SQL> exec EXECUTECMD('/usr/bin/rm -R /u05/home/tsupport/malshous/test.txt');

    In main
    Return code from process 0
    Done executing

    PL/SQL procedure successfully completed.

    SQL> !ls

  5. #5
    Join Date
    Apr 2009
    Posts
    2

    problem executing shell script

    Hi,
    Your example is good.. I tried running the example for a shell script on HP-UX
    test.sh
    #!/usr/bin/sh
    echo HELLO

    This works fine with exit code 0.

    If I change it to
    #!/usr/bin/sh
    echo HELLO > /test/log.txt
    cd /test/upload
    export ORACLE_HOME=/product/oracle
    sqlplus test/test@TESTDB @/test/upload/test.sql

    test.sql contains

    BEGIN
    INSERT INTO msg_tbl values('hi there inserted');
    commit;
    END;
    /
    quit;

    For this script I have a problem. Although the commandline shows PL/SQL procedure executed successfully, no rows are inserted to the table. However the message HELLO is found in log.txt..
    Any pointers on what exactly could be the problem.
    Not able to figure it out since the log.txt is getting created and it says proc executed but i dont see any rows.

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you resurrected a 7 year old post - must be a record

  7. #7
    Join Date
    Apr 2009
    Posts
    2
    yes... having big trouble trying to run my shell script.

    although OS commands like ls, echo etc are giving the output, I have problem executing sql script from inside my shell script.

    any ideas what could be the trouble-maker?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width