Home » SQL & PL/SQL » SQL & PL/SQL » Running OS script file from PL/SQL through java class.
Running OS script file from PL/SQL through java class. [message #333948] Tue, 15 July 2008 00:21 Go to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
I am trying to run an os script from plsql.

For this, I did

CREATE OR REPLACE AND 
COMPILE JAVA SOURCE NAMED Command 
AS
import java.io.*;
import java.util.*;
public class Command
{
 public static void run(String cmdText)
 throws IOException, InterruptedException
 {
  int rtn;
   Runtime rt = Runtime.getRuntime();
   Process prcs = rt.exec(cmdText);
   rtn = prcs.waitFor();
 }
}

CREATE OR REPLACE PROCEDURE 
runoscommand(cmd IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Command.run(java.lang.String)'


(Above codes are taken from http://www.orafaq.com/forum/?t=rview&goto=331644#msg_331649)

Permissions:
exec dbms_java.grant_permission('SCOTT','java.io.FilePermission','/home/oracle/sarwagya_test/test.sh','execute');
exec dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','writeFileDescriptor','');
exec dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','readFileDescriptor','');


BEGIN
	 runoscommand('/home/oracle/sarwagya_test/test.sh');
END;


contents of test.sh
#!/bin/bash
touch new_file.txt


the directory sarwagya_test has permission 777.

Although the plcode runs successfully, the statement inside test.sh is not executed; i.e.,new_file.txt is not created inside /home/oracle/sarwagya_test/ directory.

Please suggest where I made the mistake.

Thanks.
Re: Running OS script file from PL/SQL through java class. [message #333956 is a reply to message #333948] Tue, 15 July 2008 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think the command should be
'/bin/bash /home/oracle/sarwagya_test/test.sh'

Regards
Michel
Re: Running OS script file from PL/SQL through java class. [message #333963 is a reply to message #333956] Tue, 15 July 2008 01:03 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Even that didn't work.

I tried with permissions on both
/bin/bash
and /home/oracle/sarwagya_test/test.sh

Re: Running OS script file from PL/SQL through java class. [message #333971 is a reply to message #333963] Tue, 15 July 2008 01:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Check the ORACLE_HOME-dir.
Who says the file should/would be created in /home/oracle/sarwagya_test/ ?
Re: Running OS script file from PL/SQL through java class. [message #333976 is a reply to message #333971] Tue, 15 July 2008 01:47 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
sorry Frank.
But, it's not even in the $ORACLE_HOME directory.

I also tried this but to no avail.
#!/bin/bash
touch /home/oracle/sarwagya_test/new_file.txt
Re: Running OS script file from PL/SQL through java class. [message #333977 is a reply to message #333976] Tue, 15 July 2008 01:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Go to a random directory.
Execute the same command as you issue to your program.
Check if the file is there.
Re: Running OS script file from PL/SQL through java class. [message #333980 is a reply to message #333977] Tue, 15 July 2008 01:58 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
I didn't understand exactly.

I had tried it in /tmp previously and couldn't get the result.


If you mean to execute the command from os terminal, I have tried it. It works - creates the file in the specified directory.

Re: Running OS script file from PL/SQL through java class. [message #334042 is a reply to message #333956] Tue, 15 July 2008 04:56 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Please suggest the changes that I should make in the above procedures to see the output of ls command from procedure.

Let's see if it works with ls.
Re: Running OS script file from PL/SQL through java class. [message #334160 is a reply to message #334042] Tue, 15 July 2008 10:24 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
try this:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241
Re: Running OS script file from PL/SQL through java class. [message #334255 is a reply to message #334160] Wed, 16 July 2008 00:20 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Thanks.
I made some changes in my codes to give the output as dbms_output.
CREATE OR REPLACE AND 
COMPILE JAVA SOURCE NAMED Command 
AS 
import java.io.*; 
import java.util.*; 
public class Command
{
public static String run(String cmdText) throws IOException, InterruptedException
	{
	int rtn; 
	Runtime rt = Runtime.getRuntime();
	rt.exec(cmdText);
	Process prcs = rt.exec(cmdText);
	BufferedReader input = new BufferedReader(new InputStreamReader(prcs.getInputStream()));
	String line=null;
	String returnLine = null;
	while((line=input.readLine()) != null) {
	returnLine = returnLine + line;
	}
	rtn = prcs.waitFor(); 
	return rtn+"hello"+returnLine;
	}
};

CREATE OR REPLACE FUNCTION 
runoscommand(cmd IN VARCHAR2) 
RETURN VARCHAR2 
AS LANGUAGE JAVA
NAME 'Command.run(java.lang.String) return java.lang.String';


Please see the outputs shown below:
BEGIN
	 DBMS_OUTPUT.PUT_LINE(runoscommand('/bin/bash /tmp/test.sh'));
END;

OUTPUT:
127hellonull

BEGIN
	 DBMS_OUTPUT.PUT_LINE(runoscommand('/bin/ls /tmp/test.sh'));
END;

OUTPUT:
0hellonull/tmp/test.sh


When calling /bin/ls it runs successfully and shows return code 0. But, in case of /bin/bash it returns 127 as return code.

It looks like some kind of permission related issue.
But, donno what to do.
Do I have to grant some directory specific permissions?

Please suggest further steps.

Thanks
Re: Running OS script file from PL/SQL through java class. [message #334265 is a reply to message #334255] Wed, 16 July 2008 00:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The difference between the two is that the /bin/bash tries to execute the shell-script, whereas the /bin/ls only checks for its existence.
what does a "ls -l /tmp/test.sh" return (on the unix prompt, not from the db)?
Re: Running OS script file from PL/SQL through java class. [message #334293 is a reply to message #334265] Wed, 16 July 2008 02:02 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Definitely, there is a difference between them.
But, I wanted to know that why the code fails on /bin/bash.

Later I came to know that exit code (127) for /bin/bash means "command not found".

Should I assume that /bin/bash cannot be run from the code?

Please suggest.

Thanks.
Re: Running OS script file from PL/SQL through java class. [message #334295 is a reply to message #334293] Wed, 16 July 2008 02:11 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Just to be sure, you HAVE a /bin/bash on that machine?

Maybe it's somewhere else like /usr/local/bin?
Re: Running OS script file from PL/SQL through java class. [message #334298 is a reply to message #334295] Wed, 16 July 2008 02:18 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Yes it is in /bin

[Updated on: Wed, 16 July 2008 02:18]

Report message to a moderator

Re: Running OS script file from PL/SQL through java class. [message #334300 is a reply to message #334298] Wed, 16 July 2008 02:24 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

What happens if you send the command "cd" ?

Since ls is an external command, and cd is an internal shell command. Just for curiosity.
Re: Running OS script file from PL/SQL through java class. [message #334310 is a reply to message #334265] Wed, 16 July 2008 02:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Frank wrote on Wed, 16 July 2008 07:53

what does a "ls -l /tmp/test.sh" return (on the unix prompt, not from the db)?

Re: Running OS script file from PL/SQL through java class. [message #334312 is a reply to message #334310] Wed, 16 July 2008 03:12 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member

ls - l /tmp/test.sh

-rwxrwxrwx  1 oracle oinstall 48 Jul 16 11:34 /tmp/test.sh
Re: Running OS script file from PL/SQL through java class. [message #334315 is a reply to message #334312] Wed, 16 July 2008 03:27 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

and cd <catalog> ?
Re: Running OS script file from PL/SQL through java class. [message #334323 is a reply to message #334315] Wed, 16 July 2008 03:45 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
begin
	 dbms_output.put_line(runoscommand('cd /tmp'));
end;

exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' );


ORA-29532: Java call terminated by uncaught Java exception: java.io.IOException: can't exec: cd doesn't exist
ORA-06512: at "SCOTT.RUNOSCOMMAND", line 1
ORA-06512: at line 2
Re: Running OS script file from PL/SQL through java class. [message #334327 is a reply to message #334323] Wed, 16 July 2008 03:50 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Check this link , it might give you some hints
Re: Running OS script file from PL/SQL through java class. [message #334369 is a reply to message #334327] Wed, 16 July 2008 05:49 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Thank you everyone for your support.

Since my current requirement is to run the expdp command from pl/sql and it works fine, I have halted further steps towards finding out about "Running /bin/bash through plsql".

I was just trying to make a generic code that can run any os command.

If some other day I need it, I will definitely ask for your help.

Thanks again.
Re: Running OS script file from PL/SQL through java class. [message #334390 is a reply to message #334369] Wed, 16 July 2008 06:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sarwagya wrote on Wed, 16 July 2008 12:49
I was just trying to make a generic code that can run any os command.


Why don't you just tell everyone oracle's unix password?

You do realise that this is a major security hazard, right?

[Updated on: Wed, 16 July 2008 06:25]

Report message to a moderator

Re: Running OS script file from PL/SQL through java class. [message #334391 is a reply to message #334390] Wed, 16 July 2008 06:28 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
yeah that is.

But, I think without granting the permission on each command, no command can be run from that code.

Re: Running OS script file from PL/SQL through java class. [message #334400 is a reply to message #334391] Wed, 16 July 2008 06:40 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I think I know what the general problem might be.

The Java procedure can't run a COMMAND as such, it can only run an EXECUTABLE. (That's why for example "cd" doesn't work)

When you start bash, it tries to starts a shell process, which might not work in that context.

What could be tried is launching the bash executable with the -c parameter, which tells it not to start a shell but to just execute a command, like that for example:

/bin/bash -c /tmp/test.sh
icon5.gif  Re: Running OS script file from PL/SQL through java class. [message #435087 is a reply to message #334400] Mon, 14 December 2009 04:15 Go to previous messageGo to next message
modulo2
Messages: 8
Registered: July 2009
Location: Saudi Arabia
Junior Member
Dears,
Although I'm not the one who started this thread long ago, but I'm facing exactly the same issues & problems.

I had already the code and was running perfectly if Oracle server is running on Windows machine, and I have same need for Oracle DB running on AIX 5.2, the excat version of DB is 9.2.0.8.0 64 bit.

The grants I have executed:
begin
dbms_java.grant_permission('SCHEMAUSER', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' );
dbms_java.grant_permission('SCHEMAUSER', 'java.io.FilePermission', '/home/oracle/demo.sh', 'read ,write, execute, delete' );
dbms_java.grant_permission('SCHEMAUSER', 'java.lang.RuntimePermission', '*', 'writeFileDescriptor' );
end;



The code for Java stored procedure is as follows:
package com.bab.ee;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

public class ExecExtern
{
    protected static String newline = System.getProperty("line.separator");

    public ExecExtern()
    {
    }

    public static String executeExternal(String s0)
    {
        String result = "";

        try
        {
            Process process = Runtime.getRuntime()
                                     .exec(s0);
            int exitVal = process.waitFor();

            result = getOutputResult(process);
            result += "Exit value=" + exitVal;

        } catch (Exception e)
        {
            result += e.getMessage();
        }
        return result.trim();
    }

    protected static String getOutputResult(Process process) throws IOException
    {
        String result = "";

        BufferedReader input = new BufferedReader(new InputStreamReader(process.getInputStream()));
        String line;
        while ((line = input.readLine()) != null)
        {
            result += line + newline;
        }
        input.close();

        input = new BufferedReader(new InputStreamReader(process.getErrorStream()));
        line = "";
        while ((line = input.readLine()) != null)
        {
            result += line + newline;
        }
        input.close();
        return result;
    }

}



The function for executing is defined as:
CREATE OR REPLACE FUNCTION EXEC_COMMAND_LINE ( S0 IN VARCHAR2 )
RETURN VARCHAR2
AS LANGUAGE JAVA NAME
'com.bab.ee.ExecExtern( java.lang.String ) return java.lang.String'; 


My trials to run using script like:
begin
  -- Call the function
  :result := exec_command_line( '...COMMAND...' );
end;


Command: '/home/oracle/demo.sh'
Result:
exec failed: /home/oracle/demo.sh, Exec format error
Exit value=255

Exit value 255 means out of range so not providing much info.

I tried another command: '/bin/bash -c /home/oracle/demo.sh'
Result:
can't exec: /bin/bash doesn't exist

I tried UNIX comman: 'ls'
Result:
can't exec: ls doesn't exist

I have reviewed the link from asktom and seems very easy and my code is similar but I really don't know what is the issue.

Appriciate your time in replying.
Re: Running OS script file from PL/SQL through java class. [message #435092 is a reply to message #435087] Mon, 14 December 2009 04:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I don't think <<ALL_FILES>> is a valid permission target..
Try adding /bin/ls .

[Edit: Hm, reading on in AskTom's article, it seems it IS a valid target]

[Updated on: Mon, 14 December 2009 04:27]

Report message to a moderator

icon5.gif  Re: Running OS script file from PL/SQL through java class. [message #435096 is a reply to message #435092] Mon, 14 December 2009 04:47 Go to previous messageGo to next message
modulo2
Messages: 8
Registered: July 2009
Location: Saudi Arabia
Junior Member
I have executed this command:
dbms_java.grant_permission('SCHEMAUSER', 'java.io.FilePermission', '/bin/ls', 'read ,write, execute, delete' );


Command: '/bin/ls'
Result (very starnge I had not seen something like this before):
back_init.oracore_2478262dsierror.loginit.orainitdw.orainitjldr.orainitodsuat.orainitodsuat.ora.oldinitodsuat.ora.orglkJLDRlkODSUATon line_db_files.sqlorapwjldr.oraorapwodsuatreorg3.sqlsnapcf_odsuat.fspfileODSUAT.oraspfileodsuat.oratdpoerror.logtivoliorts.dbf

Command: 'ls'
can't exec: ls doesn't exist
icon7.gif  Re: Running OS script file from PL/SQL through java class. [message #435097 is a reply to message #435096] Mon, 14 December 2009 05:01 Go to previous messageGo to next message
modulo2
Messages: 8
Registered: July 2009
Location: Saudi Arabia
Junior Member
Sorry the previous post for some expermental code changes in Java, I revereted to the one posted in forum, and the result is:

back_init.ora
core_2478262
dsierror.log
init.ora
initdw.ora
initjldr.ora
initodsuat.ora
initodsuat.ora.old
initodsuat.ora.org
lkJLDR
lkODSUAT
online_db_files.sql
orapwjldr.ora
orapwodsuat
reorg3.sql
snapcf_odsuat.f
spfileODSUAT.ora
spfileodsuat.ora
tdpoerror.log
tivoliorts.dbf
Exit value=0


I checked with DBA this is a folder listing /u01/app/oracle/product/9.2.0/dbs Razz

Thanks very much.

Now can you help in running shell script like /home/oracle/demo.sh I made it just doing copy of some files as sample
Re: Running OS script file from PL/SQL through java class. [message #435100 is a reply to message #435097] Mon, 14 December 2009 05:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Can you execute it (as user oracle) from the command line?
replace its contents with a /bin/ls and see what happens.
If it does return a result, it is the shell-script that generates the error, not the calling-framework (apart from privs on the file to be copied, and the directory to copy it to)
Re: Running OS script file from PL/SQL through java class. [message #435101 is a reply to message #435100] Mon, 14 December 2009 05:21 Go to previous messageGo to next message
modulo2
Messages: 8
Registered: July 2009
Location: Saudi Arabia
Junior Member
Shell script is created and can be executed from oracle AIX user, the file permission is -rwxr-xr-x

I replaced the contents with only /bin/ls
Result is:
exec failed: /home/oracle/demo.sh, Exec format error
Exit value=255

There is no file copy now in script, what do you think the error is?
Re: Running OS script file from PL/SQL through java class. [message #435102 is a reply to message #435101] Mon, 14 December 2009 05:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I don't really know, since I never used such a construct.
Maybe you have to explicitly call the shell to execute it, so the command should be "/bin/sh your_shell.sh"?
icon3.gif  Re: Running OS script file from PL/SQL through java class. [message #435114 is a reply to message #435102] Mon, 14 December 2009 06:43 Go to previous message
modulo2
Messages: 8
Registered: July 2009
Location: Saudi Arabia
Junior Member
Really thanks Frank for your very quick support.

It worked fine, just to summarize what was done for others who will view this thread.

dbms_java.grant_permission('SCHEMAUSER', 'java.io.FilePermission', '/bin/sh', 'read ,write, execute, delete' );

and I passed the following in arguments:
/bin/sh /home/oracle/demo.sh

And it worked. I have tried to check if specific privilege needs to be granted for shell script commands, so I have tried command like mv, cp, rm inside the demo.sh and non of them required special permission from Oracle SYS.

Thanks very much to Frank and his direction. Smile
Previous Topic: Filter the Character
Next Topic: Easiest way to delete duplicate rows?
Goto Forum:
  


Current Time: Thu Sep 29 22:48:08 CDT 2016

Total time taken to generate the page: 0.06630 seconds