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

[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

[srinivast]

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.

[ashish23]

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

[ashish23]

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

[nagashreeb]

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.

[davey23uk]

you resurrected a 7 year old post - must be a record

[nagashreeb]

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?