Home » Developer & Programmer » JDeveloper, Java & XML » how to call java class in plsql or how to use plsql to invoke shell (2 threads merged by bb)
how to call java class in plsql or how to use plsql to invoke shell (2 threads merged by bb) [message #366205] Fri, 12 December 2008 02:29 Go to next message
sunweijie22
Messages: 14
Registered: December 2008
Junior Member
could anyone can help me? now i want to using plsql to invoke
shell.i did one script according to metlink Note:165256.1 here is my step,is there any problem i do?
1.create a java source by plsql developer:

create or replace and compile java source named test_executecmd as
import java.lang.* ;
import java.io.*;
public class test_executecmd
{
public static String run(String Command){
try{
Runtime.getRuntime().exec(Command);
return("0");
}
catch (Exception e){
System.out.println("Error running command: " + Command +
"\n" + e.getMessage());
return(e.getMessage());
}}}
2.create a function
CREATE OR REPLACE FUNCTION TEST_ExecuteCmd(Command IN STRING) RETURN number IS
LANGUAGE JAVA NAME 'test_executecmd.run(Java.lang.string)return int';
3.Connect as SYSTEM and grant the following privilege to apps by sqlplus
Execute dbms_java.grant_permission( 'APPS','SYS:java.io.FilePermission','<<ALL FILES>>','execute');

execute dbms_java.grant_permission( 'APPS','SYS:java.lang.RuntimePermission','writeFileDescriptor','*');

execute dbms_java.grant_permission( 'APPS','SYS:java.lang.RuntimePermission','readFileDescriptor','*' );
4.exec the programm

declare
v number(10);
begin
V:=TVSN_ExecuteCmd_TEST('/usr/bin/who /home/test');
dbms_output.put_line(v);
end ;

when i exec it ,the plsql developer alert error:
ora-29531:no method run in class test_executecmd
could anybody help me ?thank you !

Re: HOW TO USING PLSQL TO INVOKE SHELL [message #366223 is a reply to message #366205] Fri, 12 December 2008 03:19 Go to previous messageGo to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

please check -

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241

this link might help.

regards,
Ram.
[Please do not multipost questions]

[Updated on: Fri, 12 December 2008 03:25]

Report message to a moderator

Re: HOW TO USING PLSQL TO INVOKE SHELL [message #366227 is a reply to message #366223] Fri, 12 December 2008 03:31 Go to previous messageGo to next message
sunweijie22
Messages: 14
Registered: December 2008
Junior Member
thank you ,but i wander why this script could not be executed because this script is oracle's demo
Re: HOW TO USING PLSQL TO INVOKE SHELL [message #366256 is a reply to message #366205] Fri, 12 December 2008 05:33 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
sunweijie22 wrote on Fri, 12 December 2008 09:29

2.create a function
CREATE OR REPLACE FUNCTION TEST_ExecuteCmd(Command IN STRING) RETURN number IS
LANGUAGE JAVA NAME 'test_executecmd.run(Java.lang.string)return int';


What happens if you define the parameter as a String instead of a string?
I have no java-enabled db at hand to test myself

[Updated on: Fri, 12 December 2008 05:34]

Report message to a moderator

Re: HOW TO USING PLSQL TO INVOKE SHELL [message #366257 is a reply to message #366256] Fri, 12 December 2008 06:16 Go to previous messageGo to next message
sunweijie22
Messages: 14
Registered: December 2008
Junior Member
Thank you for your reply, but the error is the same ,
Re: HOW TO USING PLSQL TO INVOKE SHELL [message #366258 is a reply to message #366257] Fri, 12 December 2008 06:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you post a link to where you got the code from?
Re: HOW TO USING PLSQL TO INVOKE SHELL [message #366259 is a reply to message #366258] Fri, 12 December 2008 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
He said:
Quote:
i did one script according to metlink Note:165256.1

Regards
Michel
Re: HOW TO USING PLSQL TO INVOKE SHELL [message #366261 is a reply to message #366259] Fri, 12 December 2008 07:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well spotted.

I've dragged the original code back from Metalink, and that works just fine for me.

Re: how to call java class in plsql or how to use plsql to invoke shell [message #366331 is a reply to message #366205] Fri, 12 December 2008 17:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You have some mismatches between the name of the function you created and the one you called and I think some parameters in the wrong order. Java is very case-sensitive. Please see the suggested corrected code below that borrows some java from Tom Kyte's run_cmd. To test it, I executed an operating system file c:\oracle11g\test.bat that contains the command dir c:\temp. I should also point out that this creates some major security issues and it would be safer to grant permissions on individual operating system files.

SYSTEM@orcl_11g> EXECUTE DBMS_JAVA.GRANT_PERMISSION ('SCOTT', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' )

PL/SQL procedure successfully completed.

SYSTEM@orcl_11g> EXECUTE DBMS_JAVA.GRANT_PERMISSION ('SCOTT', 'java.lang.RuntimePermission', '*', 'writeFileDescriptor' )

PL/SQL procedure successfully completed.

SYSTEM@orcl_11g> EXECUTE DBMS_JAVA.GRANT_PERMISSION ('SCOTT', 'java.lang.RuntimePermission', '*', 'readFileDescriptor' )

PL/SQL procedure successfully completed.

SYSTEM@orcl_11g> CONNECT scott/tiger
Connected.
SCOTT@orcl_11g> 
SCOTT@orcl_11g> create or replace and compile java source named "Test_ExecuteCmd" as
  2  import java.lang.*;
  3  import java.io.*;
  4  public class Test_ExecuteCmd
  5  {
  6    public static int Run(String args)
  7    {
  8    Runtime rt = Runtime.getRuntime();
  9    int	  rc = -1;
 10    try
 11   {
 12  	  Process p = rt.exec(args);
 13  
 14  	  int bufSize = 4096;
 15  	  BufferedInputStream bis =
 16  	   new BufferedInputStream(p.getInputStream(), bufSize);
 17  	  int len;
 18  	  byte buffer[] = new byte[bufSize];
 19  	   // Echo back what the program spit out
 20  	  while ((len = bis.read(buffer, 0, bufSize)) != -1)
 21  	     System.out.write(buffer, 0, len);
 22  	   rc = p.waitFor();
 23    }
 24    catch (Exception e)
 25    {
 26  	  e.printStackTrace();
 27  	  rc = -1;
 28    }
 29    finally
 30    {
 31  	  return rc;
 32    }
 33    }
 34  }
 35  /

Java created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION tvns_executecmd_test
  2    (command IN VARCHAR2)
  3    RETURN NUMBER
  4  AS
  5  LANGUAGE JAVA NAME 'Test_ExecuteCmd.Run(java.lang.String) return integer';
  6  /

Function created.

SCOTT@orcl_11g> SET SERVEROUTPUT ON
SCOTT@orcl_11g> EXEC DBMS_JAVA.SET_OUTPUT (100000)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> DECLARE
  2    v NUMBER;
  3  BEGIN
  4    v := tvsn_executecmd_test ('c:\oracle11g\test.bat');
  5    DBMS_OUTPUT.PUT_LINE (v);
  6  END;
  7  /
C:\app\Barbara\product\11.1.0\db_1\DATABASE>dir c:\temp
Volume in drive C is OS
Volume Serial Number is ... -- removed
Directory of c:\temp
12/12/2008  03:29 PM    <DIR>          .
12/12/2008  03:29 PM    <DIR>          ..
10/16/2008  07:10 AM               591 EXT_TBL_EMP_2172_4752.log
10/16/2008  07:35 AM               591 EXT_TBL_EMP_2172_4876.log
10/16/2008  07:10 AM                98 EXT_TBL_EMP_2172_532.log
03/28/2008  10:30 PM                 6 PROBCK22.XML
10/22/2008  12:44 PM                41 test.dat
10/09/2008  01:43 PM                46 test.txt
10/09/2008  01:43 PM                49 test2.txt
7 File(s)          1,422 bytes
2 Dir(s)  57,254,367,232 bytes free
0

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>

[Updated on: Fri, 12 December 2008 17:42]

Report message to a moderator

Re: HOW TO USING PLSQL TO INVOKE SHELL [message #366340 is a reply to message #366205] Fri, 12 December 2008 19:22 Go to previous messageGo to next message
sunweijie22
Messages: 14
Registered: December 2008
Junior Member
really?i try to again,but is there any problem of my script?
Re: HOW TO USING PLSQL TO INVOKE SHELL [message #366341 is a reply to message #366205] Fri, 12 December 2008 20:49 Go to previous messageGo to next message
sunweijie22
Messages: 14
Registered: December 2008
Junior Member
java source is case-sensitive,so i copy the oracle's script,it runs normally,thank you for everyone who helped me!
Re: HOW TO USING PLSQL TO INVOKE SHELL [message #366344 is a reply to message #366341] Fri, 12 December 2008 22:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
sunweijie22 wrote on Fri, 12 December 2008 18:49
java source is case-sensitive,so i copy the oracle's script,it runs normally,thank you for everyone who helped me!


I wish I had known that before I wasted my time posting all the code in response to your duplicate post, which has now been merged with this one.
Re: how to call java class in plsql or how to use plsql to invoke shell (2 threads merged by bb) [message #366360 is a reply to message #366205] Sat, 13 December 2008 01:15 Go to previous messageGo to next message
sunweijie22
Messages: 14
Registered: December 2008
Junior Member
the scripts is ok,buti have another question ,when i run the script as follow in OS SQLPLUS (OS:saloris 9)
SQL> Declare
  2   x Varchar2(2000);
  3  Begin
  4   x := TESTCommand_Run(/bin/touch /d01/testappl/fnd/11.5.0/java/a.txt);
  5   DBMS_OUTPUT.Put_Line(x);
  6  End;
  7  /

PL/SQL procedure successfully completed.


But I check the directory, NO FILE have been created, why?
Re: how to call java class in plsql or how to use plsql to invoke shell (2 threads merged by bb) [message #366368 is a reply to message #366360] Sat, 13 December 2008 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: how to call java class in plsql or how to use plsql to invoke shell (2 threads merged by bb) [message #366369 is a reply to message #366368] Sat, 13 December 2008 01:51 Go to previous messageGo to next message
sunweijie22
Messages: 14
Registered: December 2008
Junior Member
i am sorry ,i forgot it,i will pay attention to it next time
Re: how to call java class in plsql or how to use plsql to invoke shell (2 threads merged by bb) [message #366371 is a reply to message #366360] Sat, 13 December 2008 02:16 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
If you logon to your solaris box as the user the Oracle processes are run in, can you execute the (exact) same command?
I have no Solaris available, but on my OSX (and if I recall correctly also on linux) touch is in /usr/bin, not in /bin
Re: how to call java class in plsql or how to use plsql to invoke shell (2 threads merged by bb) [message #366378 is a reply to message #366371] Sat, 13 December 2008 02:49 Go to previous messageGo to next message
sunweijie22
Messages: 14
Registered: December 2008
Junior Member
in my os,the command /bin/touch can create a new file
Re: how to call java class in plsql or how to use plsql to invoke shell (2 threads merged by bb) [message #375799 is a reply to message #366205] Sun, 14 December 2008 19:58 Go to previous messageGo to next message
sunweijie22
Messages: 14
Registered: December 2008
Junior Member
thank you for everyone who help me ,i found that the reason why the script couldn't run ,i lose one step, the operational directory should be registed in the table
dba_directories.
Re: how to call java class in plsql or how to use plsql to invoke shell (2 threads merged by bb) [message #405186 is a reply to message #366205] Tue, 26 May 2009 16:28 Go to previous messageGo to next message
malkimama
Messages: 2
Registered: October 2008
Junior Member
Hi:

I compiled the exact code shown in this thread and when I try to execute the function, I'm getting the below output.

-1
PL/SQL procedure successfully completed.

Can anybody explain if I'm missing anything.
Re: how to call java class in plsql or how to use plsql to invoke shell (2 threads merged by bb) [message #405198 is a reply to message #405186] Tue, 26 May 2009 23:38 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
- are you on Unix / Linux?
- Do you have /bin/touch?
- Do you have /d01?
- can you execute the same command from the command prompt instead of pl/sql?
- No sign of the stacktrace?
Previous Topic: XML to RDBMS
Next Topic: XMLSequence String match how to make it work?
Goto Forum:
  


Current Time: Thu Mar 28 04:28:54 CDT 2024