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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

brtk
Hi,

I have no idea why your code doesn't work, but...

2 hour ago I also had a similar problem with executing OS command from java and sample classes described in MOS 306106.1 helped me much.
Those classes works called from pl/sql procedure (at least in my case).


Bartek
Nagashree B
I am stuck with this problem for past 3 weeks. The proc and script run absolutely fine on Windows with oracle installed. I have trouble running it with HP-UX and encounter this error.
Can you pls point to MOS 306106.1 link.. I could not find it.
brtk
MOS means My Oracle Support aka metalink.
And the link is [http://metalink.oracle.com].
Then put the document number in search field.

To read mos documents you need to have a valid mos account.

Bartek
user11219185
Hi
I have a java stored procedure which executes a shell script. It works fine in our development environment and returns the exit code of 0. The same code doesn't seem to work fine in our production environment. The exit code returned is 2 which means misuse of shell builtin in bash programming. Any idea what might be the problem.

Thanks
brtk
You didn't provide the code called from Java. How should we know what was the reason of error 2?
If the code is able to be called from java from test database, then you must check what is the difference between test and production.
Paths? Libraries? OS privilieges?

Bartek
user11219185
Here's my Java Stored Proc

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "ExecuteOSCmdLog" as import java.awt.*;
import java.applet.*;
import java.lang.*;
import java.io.*;
import java.sql.*;

public class ExecOSCmdLog {
public static void doit(String args[])
{
boolean STDOUT = false;
boolean STDERR = false;
boolean LOGGER = true;
InputStream so = null;
InputStream se = null;
Thread o =null;
Thread e =null;

int rc = -1;
String cmd = "Error inproper call";
String log = "Logging started";
FileOutputStream fos = null;
if (args.length < 1) {
System.out.println("USAGE: java ExecOSCmdLog \'cmd\' ");
System.exit(1);
}

try {
cmd = args[0];

if (args.length >1 ) {
for (int i =1, j = args.length ; i < j; i++) {
if (args.equals("STDOUT")) { // out and log
STDOUT= true;
} else if (args[i].equals("STDERR")) { // out err and log
STDERR= true;
STDOUT= true;
} else if (args[i].equals("NOLOG")) { // out and no log
LOGGER= false;
STDOUT= true;
} else if (args[i].equals("NONE")) {
// eat the defaults
// nothing to the screen, do logging
} else {
fos = new FileOutputStream(args[1]);
STDOUT= true;
}
}
}
Runtime rt = Runtime.getRuntime();
Process p = rt.exec(cmd);
so = p.getInputStream();
se = p.getErrorStream();

if (STDOUT) {
capture(so, fos, "OUT ",o);
}
if (STDERR) {
capture(se, null, "ERR ",e);
}

try {
rc = p.waitFor();
/* Handle exceptions for waitFor() */
} catch (InterruptedException intexc) {
System.out.println("Interrupted Exception on waitFor: " +
intexc.getMessage());
}


if ( o != null) {
o.join();
o = null;
}
if ( e != null) {
e.join();
e = null;
}
if (fos !=null) {
fos.flush();
fos.close();
}
se.close();
so.close();
System.out.println("ExitValue: " + rc);


} catch (Throwable t) {
System.out.println("ExitValue: " + rc);
t.printStackTrace();
} finally {
if (LOGGER) {
LogCmd(cmd,log,rc);
STDOUT = false;
STDERR = false;
LOGGER = true;
}
}

}

private static void capture( InputStream is,
FileOutputStream fos,
String label,
Thread o ){
CaptureStream out = new CaptureStream(is, label, fos );
o = new Thread(out);
o.start();
}


private static void LogCmd(String cmd,String log, int rc){
try {
final String insert =
"INSERT INTO OS_CMD_LOG (CALLER,CMD,RETURN_VALUE)"+
" VALUES ( SYS_CONTEXT('USERENV', 'SESSION_USER' ), "+
" '"+ cmd + "', "+ rc + " ) " ;
// As the connection should not be closed anyway
Statement stmt =
DriverManager.getConnection
("jdbc:default:connection:").createStatement();
stmt.executeUpdate(insert);
stmt.close();
final String insert1 =
"INSERT INTO JOB_EXEC_LOGS (ITEM_ID,LOG_LEVEL,CHANGE_DATE,MESSAGE,LINE_NUM)"+
" VALUES ( 2431 ,"+ " 0 ,"+ " sysdate, "+ " SYS_CONTEXT('USERENV', 'SESSION_USER' ) ||"+
" ' OS Command: ' ||" + " '" + cmd + "',"+ "job_log_seq.NEXTVAL" + " ) " ;
// As the connection should not be closed anyway
Statement stmt1 =
DriverManager.getConnection
("jdbc:default:connection:").createStatement();
stmt1.executeUpdate(insert1);
stmt1.close();
} catch (SQLException e ) {
e.printStackTrace();
}
}


}
/

Hope you can help.
brtk
CALLING Java procedure seems to be ok. But in last post you complained about exit code in CALLED script.

Where the exit code (=2) is generated? If in a script, then provide a script. In Java? Well, I can't see the place where it could be.

Bartek
Shane K
Hi,
I just received the same error (Exception during creation of the process). Resolved this by granting the JAVA_DEPLOY role to the user who was attempting to run the JSP. Hope this helps someone.

Shane
514498
Hi.

I have a problem with the stored procedure at a time to execute for call a shell script on UNIX (HP-UX) attach the procedure and the shell script for help. When execute return the number 2. My oracle database is version 10.2.0.4 10gR2 on Oracle RAC running over HP-UX(11.23)

----THIS IS THE PROCEDURE-------
SQL> set serveroutput on
SQL> exec DBMS_JAVA.SET_OUTPUT(1000000)

PL/SQL procedure successfully completed.

SQL> DECLARE
2 vl_declare NUMBER(5);
3 BEGIN
4 vl_declare := K_SEG_UTILITARIOS.F_RUN_CMD ('/usr/bin/sh /u03/EnlaceBCH/Utilitarios/ generar.cmd GPG PA N');
5 DBMS_OUTPUT.put_line ('Return:'||nvl(vl_declare,'0'));
6 EXCEPTION
7 WHEN OTHERS
8 THEN
9 DBMS_OUTPUT.put_line (SQLERRM);
10 END;
11 /
Return:2

PL/SQL procedure successfully completed.


---THIS IS THE SHELL SCRIPT-----
#!/usr/bin/sh
#Variables a Utilizar#
#--------------------#
export pa_tipo_archivo=$1 #Tipo: TXT o GPG
export pa_tipo_lote=$2 #Tipos de Lote: PA,VC,OD,TC,EX
export pa_operacion=$3 #Tipo Operacion: N(No Juntar), S(Añadir)
export pa_archivo=/u03/SIAFI_DOCENTES/pruebas/listado_archivos_siafi.lst
export pa_dir_bch=/u03/SIAFI_DOCENTES/pruebas
export pa_dir_sefin=/u03/SIAFI_DOCENTES/pruebas
export pa_txt=TXT
export pa_gpg=GPG
#export pa_dir_bch=/u03/home/bch/home/bch/Home_BCH
#export pa_dir_sefin=/u03/EnlanceBCH/Home_BCH

#Cuerpo Pricipal del Shell#
#-------------------------#
#se valida el tipo de archivo si es GPG o TXT
if [ $pa_tipo_archivo = "GPG" ];then
#se valida el tipo de operacion
if [ $pa_operacion = "N" ];then
#Se valida el tipo de lote
if [ $pa_tipo_lote = "PA" ];then
rm /usr/bin/$pa_archivo
#Se cambia al directorio
#cd /usr/bin/$pa_dir_bch/TRB
#Se listan los GPGP
for i in $(/usr/bin/ls *.gpg);do
echo $pa_tipo_archivo","$i","$pa_tipo_lote >> $pa_archivo
done
elif [ $pa_tipo_lote = "VC" ];then
rm /usr/bin/$pa_archivo
#Se cambia al directorio
#cd /usr/bin/$pa_dir_bch/VALIDACION
#Se listan los GPGP
for i in $(/usr/bin/ls *.gpg);do
echo $pa_tipo_archivo","$i","$pa_tipo_lote >> $pa_archivo
done
elif [ $pa_tipo_lote = "OD" ];then
rm /usr/bin/$pa_archivo
#Se cambia al directorio
#cd /usr/bin/$pa_dir_bch/OSDE
#Se listan los GPGP
for i in $(/usr/bin/ls *.gpg);do
echo $pa_tipo_archivo","$i","$pa_tipo_lote >> $pa_archivo
done
elif [ $pa_tipo_lote = "TC" ];then
rm /usr/bin/$pa_archivo
#Se cambia al directorio
#cd $pa_dir_bch/TEC
#Se listan los GPGP
for i in $(/usr/bin/ls *.gpg);do
echo $pa_tipo_archivo","$i","$pa_tipo_lote >> $pa_archivo
done
elif [ $pa_tipo_lote = "EX" ];then
rm /usr/bin/$pa_archivo
#Se cambia al directorio
#cd $pa_dir_bch/RECFIS
#Se listan los GPG
for i in $(/usr/bin/ls *.gpg);do
echo $pa_tipo_archivo","$i","$pa_tipo_lote >> $pa_archivo
done
fi
else
#se pasa al directorio de TXT
cd /usr/bin/$pa_dir_sefin

#se listan los archivos txt
for i in $(/usr/bin/ls *.txt);do
echo $pa_txt","$i",">>$pa_archivo
done
fi
else
if [ $pa_operacion = "S" ];then
#Se valida el tipo de operacion
if [ $pa_tipo_lote = "PA" ];then
#Se cambia al directorio
#cd $pa_dir_bch/TRB
#Se listan los GPGP
for i in $(/usr/bin/ls *.gpg);do
echo $pa_gpg","$i","$pa_tipo_lote >> $pa_archivo
done
elif [ $pa_tipo_lote = "VC" ];then
#Se cambia al directorio
#cd $pa_dir_bch/VALIDACION
#Se listan los GPGP
for i in $(/usr/bin/ls *.gpg);do
echo $pa_gpg","$i","$pa_tipo_lote >> $pa_archivo
done
elif [ $pa_tipo_lote = "OD" ];then
#Se cambia al directorio
#cd $pa_dir_bch/OSDE
#Se listan los GPGP
for i in $(/usr/bin/ls *.gpg);do
echo $pa_gpg","$i","$pa_tipo_lote >> $pa_archivo
done
elif [ $pa_tipo_lote = "TC" ];then
#Se cambia al directorio
#cd $pa_dir_bch/TEC
#Se listan los GPGP
for i in $(/usr/bin/ls *.gpg);do
echo $pa_gpg","$i","$pa_tipo_lote >> $pa_archivo
done
elif [ $pa_tipo_lote = "EX" ];then
#Se cambia al directorio
#cd $pa_dir_bch/RECFIS
#Se listan los GPG
for i in $(/usr/bin/ls *.gpg);do
echo $pa_gpg","$i","$pa_tipo_lote >> $pa_archivo
done
fi
else
#se borra archivo
if [ -f $pa_archivo ];then
/usr/bin/rm $pa_archivo
fi

#se listan los archivos txt
for i in $(/usr/bin/ls *.txt);do
echo $pa_tipo_archivo","$i",">>$pa_archivo
done
fi
fi

If you could tell me where is the error, I will apriacete.

Thanks....
824700
Hi all,

I have a similar problem.

In windows environment I can call a batch file from an oracle stored procedure (loaded java class).
However, the same setup does not work on unix - on unix I am using shell scripts with the same logic as in my windows batch. The procedure is called successfully, but the shell script is not running... I can run my shell script directly from the unix command line successfully, so the script is ok.

Could anyone, please, advice on what could be the difference in calling shell script in unix vs. batch in windows from Oracle Stored Procedures? Could it be that the Oracle user in UNIX box should have some privileges granted to run shell scripts?

Thanks in advance,
user1093647
I used the java program at the top of the thread and it was working great for some commands like ls or cat, but it kept returning 255 if i tried to run a script or print. It turned out when the process was opened there is no PATH set. So, i simply preceeded the command with ksh (ksh /app/oracle/testj.sh) and it works great now.
1 - 11
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,201 views