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
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.