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.