Skip to Main Content
  • Questions
  • Running os command/program from java

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mahomed.

Asked: March 15, 2002 - 7:43 am UTC

Last updated: December 17, 2012 - 3:13 pm UTC

Version: 8.1.7.0.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I am trying to run a c program that outputs semaphore values using java stored procedure on Unix OSF V4.0 alpha.

I have the following java stored procedure :

CREATE OR REPLACE and COMPILE JAVA SOURCE
NAMED "MyCommand"
AS
import java.io.*;
import java.lang.*;

public class MyCommand extends Object
{

public static int RunThis(String[] args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;

try
{
Process p = rt.exec(args[0]);

int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];

// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);

rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/

create or replace function RUN_CMD( p_cmd in varchar2)
return number
AS LANGUAGE JAVA
NAME 'MyCommand.RunThis(java.lang.String[]) return integer';
/

create or replace procedure CMD(p_cmd in varchar2)
as
x number;
begin
x := RUN_CMD(p_cmd);
end;
/


I have granted the following permissions :

begin
dbms_java.grant_permission
('MIS',
'java.io.FilePermission',
'/usr2/vfk_dev/exe/*',
'execute');

dbms_java.grant_permission
('MIS',
'java.io.FilePermission',
'/tmp',
'write');


dbms_java.grant_permission
('MIS',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;
/



When I run the following command, I do not see the output file tstsem.out in dir /tmp
select RUN_CMD('/usr2/vfk_dev/exe/tstsem | head > /tmp/tstsem.out ') from dual;

RUN_CMD('/USR2/VFK_DEV/EXE/TSTSEM|HEAD>/TMP/TSTSEM.OUT')
--------------------------------------------------------
0

SQL> !ls /tmp
OraInstall plnlay12545.log tmp30410.sql
plnlay12545.bad tmp25804.sql tmp31246.sql

SQL> !/usr2/vfk_dev/exe/tstsem | head
tstsem: Semaphore values are :
sem 00 = 1 pid = 25700 ncnt = 0 zcnt = 0
sem 01 = 1 pid = 25700 ncnt = 0 zcnt = 0
sem 02 = 1 pid = 25700 ncnt = 0 zcnt = 0
sem 03 = 1 pid = 18450 ncnt = 0 zcnt = 0
sem 04 = 1 pid = 25700 ncnt = 0 zcnt = 0
sem 05 = 1 pid = 25700 ncnt = 0 zcnt = 0
sem 06 = 1 pid = 6639 ncnt = 0 zcnt = 0
sem 07 = 1 pid = 25700 ncnt = 0 zcnt = 0
sem 08 = 1 pid = 25700 ncnt = 0 zcnt = 0



What is the problem? Can you help?

Thanks
Mahomed


and we said...

Quite simply because you are confusing "rt.exec" with the UNIX shell!

>, !, <, etc -- they are all things that mean something to /usr/bin/sh or /usr/bin/csh and so on. They mean *nothing* normally. Unless you have a command shell in place to interpret them -- all you have it a garbage command.

You could run the commands via the shell -- but that would be a terribly BAD idea (you would have to grant permissions to run /usr/bin/sh -- that would mean you could run ANY COMMAND on the machine).

Suggest you set up a shell script like this:

$ cat test.sh
#!/bin/sh

/usr/bin/ipcs -a | /usr/ucb/head > /tmp/ipcs.out

$

and then use these perms and command to run it:

test@ORA817DEV.US.ORACLE.COM> begin
2 dbms_java.grant_permission
3 ('TEST',
4 'java.io.FilePermission',
5 '/export/home/ora817/bin/test.sh',
6 'execute');
7
8 dbms_java.grant_permission
9 ('TEST',
10 'java.lang.RuntimePermission',
11 '*',
12 'writeFileDescriptor' );
13 end;
14 /

PL/SQL procedure successfully completed.

test@ORA817DEV.US.ORACLE.COM>
test@ORA817DEV.US.ORACLE.COM> set serveroutput on size 1000000
test@ORA817DEV.US.ORACLE.COM> exec dbms_java.set_output( 1000000 );

PL/SQL procedure successfully completed.

test@ORA817DEV.US.ORACLE.COM> !ls -l /tmp/ipcs.out
/tmp/ipcs.out not found

test@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( run_cmd( '/export/home/ora817/bin/test.sh' ) );
Normal return, rc = 0
0

PL/SQL procedure successfully completed.

test@ORA817DEV.US.ORACLE.COM> !ls -l /tmp/ipcs.out
-rw-r--r-- 1 ora817 1020 Mar 15 09:32 /tmp/ipcs.out





Rating

  (24 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Mahomed

A reader, March 15, 2002 - 10:37 am UTC


Yep! Tom you have solved the problem, Thanks very much !

running on Windows NT

Chris Soza, April 04, 2002 - 6:36 am UTC

Tom,

Is it possible to use a similar java procedure to copy files using UNC paths ?. If so can you give an example. I used the same sp as in this example and although it allows to copy files from the same server it doesn't seem to be able to use UNC paths.

Rgds, Chris

Tom Kyte
April 04, 2002 - 12:56 pm UTC

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:241814624807 <code>
for what the issue is and think 13 times before doing this. On NT -- network drives are done very differently then on Unix. It's not as easy as a mount.

Problem with java.lang.ArrayIndexOutOfBoundsException

Zhuang Li, April 10, 2002 - 5:01 am UTC

This example will be very useful for us for running a OS command with in a plsql program.

I followed the example and created all the java stored procedure and functions successfully. Also I have given the permissions as advised in the note. But on the last step I got error:

=====================================================
legd> !cat /tmp/zl.sh
#!/bin/ksh
/usr/bin/ipcs -a | /usr/ucb/head >/tmp/ipcs.out

legd> !ls -l /tmp/ipcs.out
/tmp/ipcs.out: No such file or directory

legd> exec dbms_output.put_line(run_cmd('/tmp/zl.sh'));
java.lang.ArrayIndexOutOfBoundsException
at MyCommand.RunThis(MyCommand:13)
-1
=====================================================
My MyCommand java stored procedure is exactly same as the example as below:
--------------------------------------------------
CREATE OR REPLACE and COMPILE JAVA SOURCE
NAMED "MyCommand"
AS
import java.io.*;
import java.lang.*;

public class MyCommand extends Object
{

public static int RunThis(String[] args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;

try
{
Process p = rt.exec(args[0]);

int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];

// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/
============================================
The line 13 is ==> int rc = -1;

Please advise.

Thank you

Zhuang







Tom Kyte
April 10, 2002 - 8:50 am UTC

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:952229840241 <code>

and look for the word bounds on that page.

Mahomed, April 10, 2002 - 7:19 am UTC

Followup to previous question.

How would you pass parameters to the shell script.

Tom Kyte
April 10, 2002 - 8:53 am UTC

just add them after the script name, as normal.

if you look at the page I referenced above (fixed the array bounds issue) -- I do an ls on a specific directly -- passing parameters to ls

A reader, December 17, 2002 - 10:54 am UTC

Hi Tom,
I am having problem with executing OS command.

gaurang@oracle9>EXEC RC ('exp userid=gaurang/gaurang tables=external_table file=E:\ADMIN');
java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute)
' )
at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
at java.security.AccessController.checkPermission(AccessController.java)
at java.lang.SecurityManager.checkPermission(SecurityManager.java)
at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java)
at java.lang.SecurityManager.checkExec(SecurityManager.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at Util.RunThis(Util.java:11)

PL/SQL procedure successfully completed.



I gave user GAURANG all required permission on server.

EXEC DBMS_JAVA.GRANT_PERMISSION(USER,'java.io.FilePermission','E:\ADMIN','read,write');

EXEC DBMS_JAVA.GRANT_PERMISSION(USER,'java.io.FilePermission','E:\ADMIN','read,write');

EXEC dbms_java.grant_permission(USER, 'java.lang.RuntimePermission','*', 'writeFileDescriptor' );

We have ORACLE HOME directory E:\ORACLE on server

I have checked DBA_JAVA_POLICY table and it is showing me all permission.

Please advice.

Thanks



Tom Kyte
December 18, 2002 - 10:34 am UTC

don't see any execute privs on exp being given? Look at the original example:

test@ORA817DEV.US.ORACLE.COM> begin
2 dbms_java.grant_permission
3 ('TEST',
4 'java.io.FilePermission',
5 '/export/home/ora817/bin/test.sh',
6 'execute');


you are missing the execute -- and suggest you only use explicit paths (to avoid trojan horses and to make sure you are doing what you thing you are doing)

A reader, December 17, 2002 - 11:03 am UTC

Tom ,

I forgot to add one permission

EXEC DBMS_JAVA.GRANT_PERMISSION(USER,'java.io.FilePermission','E:\ORACLE\BIN\EXP.EXE,'EXECUTE');

Thanks,



Hi Tom is a way to give permission to all the directories

juancarlosreyesp@yahoo.com, August 21, 2003 - 7:26 pm UTC

Hi Tom is a way to give permission to any file and directory, instead of file by file
Instead

EXEC
DBMS_JAVA.GRANT_PERMISSION(USER,'java.io.FilePermission','E:\ORACLE\BIN\EXP.EXE'
EXECUTE');

Something like
EXEC
DBMS_JAVA.GRANT_PERMISSION(USER,'java.io.FilePermission','%'
EXECUTE');

Thank you

Tom Kyte
August 21, 2003 - 8:48 pm UTC

OUCH

'*' works too -- but -- do you "grant select any table to public" in your database? (lets hope not). you "grant select on emp to <role|user>"

wildcards are "big"

hmm, dbshut be in $OH/bin....
hmm, rm be in /usr/bin...

do you really want that?


Thank :)

A reader, August 22, 2003 - 9:17 am UTC

Yes that is what I wanted to know

RICHARD, November 20, 2003 - 10:21 pm UTC

Hi Tom,

Thank you very much for your previous help.

Regarding your wonderful RC routine (java stored procedure) to run OS command in your book. I have deployed all related objects and everything run perfectly.

Due to security concern, we would like to use dbms_java.grant_permission as secured as possible,

For moving files from one directory to the other, we only talked about move file here,
begin
rc('c:\winnt\system32\cmd.exe /c move c:\from\fromtest1.xml
c:\to\fromtest1.xml');
end;

What will be most secured way to do DBMS_JAVA.GRANT_PERMISSION
instead of
BEGIN
DBMS_JAVA.GRANT_PERMISSION
('USER',
'java.io.FilePermission',
'c:\winnt\system32\cmd.exe,
'execute');
END;

For example, can we do

BEGIN
DBMS_JAVA.GRANT_PERMISSION
('USER',
'java.io.FilePermission',
'c:\winnt\system32\cmd.exe /c move c:\from\*.xml :\to\*.xml,
'execute');
END;

Please advise the most secured way...

Thanks for help.

Tom Kyte
November 21, 2003 - 4:44 pm UTC

no no -- don't give cmd.exe perms!!! you can do anything then!

anything.

either

a) write a script that does this
b) use xcopy and grant on xcopy -- so you can only copy files (and be careful someone doesn't do "xcopy foo.dat c:\oracle\dbs\system.dbf"!!!)

java io

Naveen Vallamkondu, May 10, 2004 - 8:38 pm UTC

Here is my code some how it doesn't work either on 8.1.7 or 9.2.0.4. I got error message "java.io.FileNotFoundException: Permission denied". Any ideas why grant_permission doesn't work.

Thanks in advance.

CREATE OR REPLACE and COMPILE JAVA SOURCE
NAMED "Oscar"
AS
import java.io.*;
import java.lang.*;
public class Oscar
{
// return a quotation from Oscar Wilde
public static String quote() {
FileOutputStream out; // declare a file output object
PrintStream p; // declare a print stream object
try
{
// Create a new file output stream
// connected to "myfile.txt"
out = new FileOutputStream("/opt/mis/apps/oraApps/OAKWCAP2/javatst.txt");
// Connect print stream to the output stream
p = new PrintStream( out );
p.println ("This is written to a file");
p.close();
out.close();
}
catch (Exception e)
{
System.err.println (e);
}
System.out.println ("Testing.....");
return "I can resist everything except temptation.";
}
}
/

CREATE or REPLACE FUNCTION oscar_quote RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'Oscar.quote() return java.lang.String';
/

begin
dbms_java.grant_permission
( 'APPS',
'java.io.FilePermission',
'/opt/mis/apps/oraApps/OAKWCAP2/-',
'write' );
end;
/
declare
x varchar2(100);
begin
dbms_java.set_output(2000);
x := oscar_quote();
dbms_output.put_line(x);
end;
/

Tom Kyte
May 11, 2004 - 8:06 am UTC

works fine for me.  perhaps the Oracle software owner does not have privs to write to that directory (it runs as "oracle", not as "you")

when i run this against a directory ORACLE (that account running my software) is not allowed to write to, I do get:

PL/SQL procedure successfully completed.
 
<b>java.io.FileNotFoundException: Permission denied</b>
Testing.....
I can resist everything except temptation.


which should be expected.



ops$tkyte@ORA9IR2> crEATE OR REPLACE and COMPILE JAVA SOURCE
  2  NAMED "Oscar"
  3  AS
  4  import java.io.*;
  5  import java.lang.*;
  6  public class Oscar
  7  {
  8    // return a quotation from Oscar Wilde
  9    public static String quote() {
 10      FileOutputStream out; // declare a file output object
 11                  PrintStream p; // declare a print stream object
 12                  try
 13                  {
 14                          // Create a new file output stream
 15                          // connected to "myfile.txt"
 16                          out = new
 17  FileOutputStream("/tmp/javatst.txt");
 18                          // Connect print stream to the output stream
 19                          p = new PrintStream( out );
 20                          p.println ("This is written to a file");
 21                          p.close();
 22              out.close();
 23                  }
 24                  catch (Exception e)
 25                  {
 26                          System.err.println (e);
 27                  }
 28                                  System.out.println ("Testing.....");
 29      return "I can resist everything except temptation.";
 30     }
 31  }
 32  /
 
Java created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE or REPLACE FUNCTION oscar_quote RETURN VARCHAR2
  2  AS LANGUAGE JAVA
  3  NAME 'Oscar.quote() return java.lang.String';
  4  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2              dbms_java.grant_permission
  3              ( USER,
  4               'java.io.FilePermission',
  5               '/tmp/-',
  6               'write' );
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect /
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2  x varchar2(100);
  3  begin
  4  dbms_java.set_output(2000);
  5  x := oscar_quote();
  6  dbms_output.put_line(x);
  7  end;
  8  /
Testing.....
I can resist everything except temptation.
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> !ls -l /tmp/java*
-rw-rw-r--    1 ora9ir2  ora9ir2        26 May 11 07:53 /tmp/javatst.txt
 

Thank you.

Naveen Vallamkondu, May 11, 2004 - 2:13 pm UTC

Thank you Tom, I have changed the directory where the user account "oracle" has write privileges and it worked just fine. These are my observations..

When I picked the directory that has privileges drwxrwxr-x, which was created by user account: applmgr but this user belongs to the group "DBA" and so the "oracle" user account as well. When you look at the permissions it does say groups does have write access to this directory but why does it say permission denied.

Tom Kyte
May 11, 2004 - 3:22 pm UTC

go to the OS

log in as "oracle"

touch a file in that directory. can you write to it then? if not, no joy from java either.

Where are these privileges stored?

A reader, August 30, 2004 - 10:40 am UTC

How can we see a list of privileges granted to a user from dbms_java.grant_permission?

Thanks!


Tom Kyte
August 30, 2004 - 10:45 am UTC

dba_java_policy/user_java_policy.

can you help diagnose this?

A reader, September 07, 2004 - 5:21 pm UTC

scott@LTST.NERI.ORG> exec rc('exp userid=scott/password TABLES=(emp, dept) rows=y file=d:\oracle\test_exp.dmp');
java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute)
has not been granted by dbms_java.grant_permission to
SchemaProtectionDomain(SCOTT|PolicyTableProxy(SCOTT))
at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
at java.security.AccessController.checkPermission(AccessController.java)
at java.lang.SecurityManager.checkPermission(SecurityManager.java)
at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java)
at java.lang.SecurityManager.checkExec(SecurityManager.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at Util.RunThis(Util:14)

smccarthy@LTST.NERI.ORG> l
1* select * from dba_java_policy where grantee = 'SCOTT'
smccarthy@LTST.NERI.ORG> /

KIND GRANTEE TYPE_ TYPE_NAME NAME ACTION ENABLED
-------- ---------- ----- ------------------------------ -------------------- -------------------- -
GRANT SCOTT SYS java.io.FilePermission d:\oracle\ORA817\bin execute ENABLED
\exp.exe

GRANT SCOTT SYS java.lang.RuntimePermission * writeFileDescriptor ENABLED

smccarthy@LTST.NERI.ORG>


Tom Kyte
September 08, 2004 - 7:52 am UTC

and if you exec rc( 'd:\oracle\ORA817\bin\exp.exe userid=........

what happens then?

Doh!

Susan, September 08, 2004 - 9:59 am UTC

Thanks Tom.

Can rc be used to run java.

monish, January 02, 2008 - 1:47 am UTC

Tom,

I am trying to execute the following:
exec rc('c:\windows\system32\cmd.exe /c C:\PROGRA~1\Java\jdk1.5.0_10\bin\java.exe ButtonEncryption C:\OpenSSL\bin\my-pubcert.pem C:\OpenSSL\bin\my-prvkey.p12 C:\OpenSSL\bin\paypal_cert_pem.txt password "cmd=_cart,upload=1,business=abc@abc.com,item_name_1=cool item 1,item_number_1=123456,quantity_1=1,amount_1=29.99,currency_code=USD,shipping=10.95,cert_id=ABCDEFGHIJ,cancel_return= http://www.abc.com/a?b=1&c=1" C:\test\java_bn.html');

However nothing really gets executed.

At C Prompt:

C:\PROGRA~1\Java\jdk1.5.0_10\bin\java.exe ButtonEncryption C:\OpenSSL\bin\my-pubcert.pem C:\OpenSSL\bin\my-prvkey.p12 C:\OpenSSL\bin\paypal_cert_pem.txt password "cmd=_cart,upload=1,business=abc@abc.com,item_name_1=cool item 1,item_number_1=123456,quantity_1=1,amount_1=29.99,currency_code=USD,shipping=10.95,cert_id=ABCDEFGHIJ,cancel_return= http://www.abc.com/a?b=1&c=1" C:\test\java_bn.html

runs fine.

Please advise.

Thanks
Tom Kyte
January 02, 2008 - 11:18 am UTC

java is just a program.

java is just like /bin/ls in that regard

so yes, assuming the environment and everything else is set correctly, java.exe could be executed.


Now, you are running cmd.exe - that would be a security hole as big as the moon. If you grant your schema the ability to run cmd.exe, you have just granted it the ability to RUN ANYTHING ON THAT MACHINE as the user your database is running as. Stop doing that - now. Do NOT use cmd.exe


and of course, your java program isn't doing anything interactive with the end user is it. Looks like it might be because you pass html????!?! into it???

you do know that anything interactive (anything that requires a screen) just cannot work right?

Found the problem.

Monish, January 02, 2008 - 2:43 am UTC

Found the problem to my earlier question.
The classpath was not getting set.

Need help

kunal Narkhede, April 21, 2011 - 9:28 am UTC

I am able to execute the script from PL/SQL however that script has to execute a following command
su - oracle -c 'emctl start blackout bkp1' everything in script is executing except this command..
Please help me..
Tom Kyte
April 25, 2011 - 8:16 am UTC

sorry?

My car won't start.

When you tell me why my car won't start, I'll help you out.


Think about it, if I told you "I'm running this command: faddfaafdas -afa 'hello world', and it is not executing - please help me out"

would you be able to?

How is it failing, what are the symptons, what is the error.

Need help

A reader, April 26, 2011 - 7:39 am UTC

That's what exactly my problem is. I am not getting any error message any where. I have a grid agent installed under os user named "oragc" and we have databases installed under os user named "oradb". I am trying to execute "emctl status agent" command from pl/sql. I have this command written in my shell script which is owned by oragc user as "sudo -u oragc emctl status agent". I got password less sudo set. and I have 775 privileges on the script at os level. the script has other commands like ls, pwd etc. all are executing and I can see output on sqlplus prompt but for this sudo command. no errors nothing.
On another test server I had agent and database installed under same OS user and script works fine. because of no error message in above mentioned scenario, I am directionless. I tried setting suid bit also, but it didn't work out.
Tom Kyte
April 26, 2011 - 2:29 pm UTC

make sure you are capturing some sort of debug, typically - errors are written to stderr, get that captured somewhere.

I cannot do anything without some sort of debug - and there will be some, you just have to capture it.

make sure stdout and stderr are going somewhere you can see.

Data Dictionary for dbms_java

Hariharan, July 02, 2012 - 3:25 am UTC

Is there any data dictionary to view whether permission is granted or not for dbms_java.grant_permission.

Return Code is -1

Amit, December 11, 2012 - 12:54 pm UTC

Hello All,

I want to run copy command (Unix) from PL SQL.
So far I did follwoing:
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED WASABI."Util" as import java.io.*; --- No change in it


CREATE OR REPLACE function WASABI.EXECUTE_SYSTEM_COMMAND ( cmd in varchar2 )
return number
as
language java

-- name 'Util.RunThis( java.lang.String ) return integer' ;
name 'org/apache/regexp/REUtil.RunThis( java.lang.String ) return integer' ;
/

Here is my PL SQL script which call copy command as Follow:

Declare

COMMAND_LINE varchar2(32767) ;
RETURN_CODE integer := 0;

begin

COMMAND_LINE := '/bin/cp test.txt test.out';

RETURN_CODE := EXECUTE_SYSTEM_COMMAND( COMMAND_LINE ) ;
DBMS_OUTPUT.PUT_LINE( 'Return Code is ' || RETURN_CODE) ;
if ( RETURN_CODE = 100 ) then
DBMS_OUTPUT.PUT_LINE( 'Return Code is a warning - review ' || RETURN_CODE) ;
elsif ( RETURN_CODE <> 0 ) then
DBMS_OUTPUT.PUT_LINE( 'ERROR - encountered when executing ; exit code = [' || RETURN_CODE || ']' ) ;
end if ;


EXCEPTION
when others then

DBMS_OUTPUT.PUT_LINE('Unix copy command failed: ' ||sqlerrm);

end;
/

I get message
PL/SQL procedure successfully completed.
but did not copy that file and also its return code is -1.

When I try
COMMAND_LINE := '/usr/bin/sh testpl.sh';

Here is testpl.sh file.

#!/bin/sh
set -x
cp test.txt test.out

Please let me know if I miss something or doing wrong.

Thanks,
Tom Kyte
December 17, 2012 - 3:13 pm UTC


EXCEPTION
   when others then

      DBMS_OUTPUT.PUT_LINE('Unix copy command failed: ' ||sqlerrm);


I cannot tell you how much I hate your code, why do you do that? why???? what is the possible purpose? it is the worst programming practice on the planet. what is the reasoning here? what is the purpose? all you did was

a) turn an error into "not an error"
b) hide the line number of the error
c) suppress much of the error stack
d) made this harder to debug




I don't see any grants from dbms_java, post entire example - not just snippets.

Exec running OS commands as Grid user, not Oracle.

A reader, July 29, 2014 - 3:15 pm UTC

Hi Tom, I am trying to execute a shell script on the OS using Runtime.getRuntime().exec.

---------------------
CREATE OR REPLACE procedure pr_executa_host(p_cmd varchar2)
as language java name 'Util.RunThis(java.lang.String)';
/
---------------------
public class Util extends Object
{

public static int RunThis(java.lang.String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;

try
{
Process p = rt.exec(args);

int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];

// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);

rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/
---------------------

The permissions granted on java to user SCOTT:

---------------------
kind grantee type name action

GRANT SCOTT java.io.FilePermission /webstart/mn500/* readFileDescriptor
GRANT SCOTT java.io.FilePermission /webstart/mn500/* read,write,execute
GRANT SCOTT java.io.FilePermission /webstart/mn500/* writeFileDescriptor
GRANT SCOTT java.io.FilePermission /webstart/mn500/CONCLUIDO/MN457560/executa.sh execute
GRANT SCOTT java.lang.RuntimePermission * writeFileDescriptor
GRANT SCOTT java.lang.RuntimePermission /webstart/mn500/CONCLUIDO/MN457560/executa.sh execute
---------------------


The shell script executa.sh, which is the one I'm trying to execute:

---------------------
#!/bin/sh
echo i am `/usr/bin/whoami`
echo environment `/bin/env`
/bin/date>>/webstart/mn500/CONCLUIDO/MN457560/test.txt
---------------------

The permissions on the directory:
---------------------
p08[oracle] $ ls -larth /webstart/mn500/CONCLUIDO/MN457560
-rw-r--r-- 1 oracle oinstall 1 Jul 29 12:03 test.txt
-rwxr-xr-x 1 oracle orafiles 430 Jul 29 12:04 executa.sh
drwxr-xr-x 2 oracle orafiles 4.0K Jul 29 12:04 .
---------------------

The thing is, when I execute the procedure pr_executa_host, it runs the shell script as grid os user, not oracle! (although it keeps oracle environment variables, like it did a 'su grid -m' before executing the shell script)

Since grid doesn't have write privileges on neither the directory, nor the file, the script doesn't do anything. Take a look:

---------------------
begin
dbms_java.set_output(1000000);
pr_executa_host('/webstart/mn500/CONCLUIDO/MN457560/executa.sh');
dbms_lock.sleep(2);
end;
/

i am grid
environment HOSTNAME=p08.XXXXXXXXXXXX.com.br SHELL=/bin/bash TERM=xterm HISTSIZE=1000 SSH_CLIENT=10.141.112.28 56029 22 NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 QTDIR=/usr/lib64/qt-3.3 QTINC=/usr/lib64/qt-3.3/include SSH_TTY=/dev/pts/0 USER=oracle LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=01;05;37;41:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lz=01;31:*.xz=01;31:*.bz2=01;31:*.tbz=01;31:*.tbz2=01;31:*.bz=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.rar=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=01;36:*.au=01;36:*.flac=01;36:*.mid=01;36:*.midi=01;36:*.mka=01;36:*.mp3=01;36:*.mpc=01;36:*.ogg=01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga=01;36:*.spx=01;36:*.xspf=01;36: ORACLE_SID=sigepshm ORACLE_BASE=/oracle ORACLE_HOSTNAME=P08 PATH= MAIL=/var/spool/mail/oracle TNS_ADMIN=/grid/product/11.2.0/grid/network/admin PWD=/oracle/product/11.2.0/db/dbs KDE_IS_PRELINKED=1 LANG=en_US.UTF-8 ORA_NET2_DESC=27,30 KDEDIRS=/usr ORACLE_TERM=xterm ORACLE_SPAWNED_PROCESS=1 HISTCONTROL=ignoredups SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass HOME=/home/oracle SHLVL=2 GRID_HOME=/oracle/product/11.2.0/grid LOGNAME=oracle CVS_RSH=ssh QTLIB=/usr/lib64/qt-3.3/lib SSH_CONNECTION=10.141.112.28 56029 10.147.0.8 22 CLASSPATH=/oracle/product/11.2.0/db/JRE:/oracle/product/11.2.0/db/jlib:/oracle/product/11.2.0/db/rdbms/jlib LESSOPEN=|/usr/bin/lesspipe.sh %s DISPLAY=localhost:10.0 ORACLE_HOME=/oracle/product/11.2.0/db G_BROKEN_FILENAMES=1 _=/bin/env
---------------------


Why is the java inside the database calling unix commands as grid user, not oracle?

Daniel Stolf, July 29, 2014 - 6:33 pm UTC

BTW, the same code ran fine on a single instance database with no grid infra (datafiles on FS). After we migrated the database to ASM, we started facing this problem.

Daniel Stolf, July 29, 2014 - 8:13 pm UTC

Tom, I figured what was happening. The execution was going through EXTPROC, and thus through the Grid Listener as grid user. Creating a second listener on the DB HOME fixed the problem, now I just have to figure out if I keep the second listener, or change the FS permissions.

Kind regards,
Daniel Stolf

what's happening here?

Michael, August 27, 2014 - 3:17 pm UTC

Calling Stack:
PL/SQL Block
-> Java Runtime.Exec
--> Shell Script
---> imp job with ignore = y


Question: 
Why can the PL/SQL Block see the value (1) loaded by imp? It should see the old value (999) due to read consistency = beginning of PL/SQL block, no?


Code:
select * from t_import;
update t_import set col1 = 999;
commit;
select * from t_import;

prompt ====== start block ======
declare
  ret number:=0;
begin
  dbms_java.set_output(10000);
  dbms_output.put_line ('IN BLOCK: BEFORE IMPORT');

  -- Import starten
  ret:=kutil.run_prog(program_name => '/tmp/imp_zvo_sync '||
                                      'rt/rt@db01 '||
                                      '/tmp/t_import.dmp',
                      arguments    => '',
                      sync         => true);
  dbms_output.put_line('IN BLOCK: RETURN VALUE: '||to_char(ret));

  -- Zugriff auf t_import im selben block
  select min(col1)
  into ret
  from t_import;

  dbms_output.put_line('IN BLOCK: Col1 Value: '||to_char(ret));
end;
/
prompt ====== end block ======

-- Zugriff auf t_import ausserhalb des Blocks
select * from t_import;

commit;

-- Zugriff auf t_import in einer neuen Transaktion
select * from t_import;



Result:
SQL> @test4.sql

      COL1
----------
         1


1 row updated.


Commit complete.


      COL1
----------
       999

====== start block ======
IN BLOCK: BEFORE IMPORT
IN BLOCK: RETURN VALUE: 0
IN BLOCK: Col1 Value: 1

PL/SQL procedure successfully completed.

====== end block ======

      COL1
----------
       999
         1


Commit complete.


      COL1
----------
       999
         1

More to Explore

DBMS_JAVA

More on PL/SQL routine DBMS_JAVA here