Skip to Main Content

Java and JavaScript in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Java stored procedure to execute shell script

Nagashree BMay 29 2009 — edited Jan 6 2012
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.

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 3 2012
Added on May 29 2009
11 comments
11,242 views