| 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  |
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 #366256 is a reply to message #366205] |
Fri, 12 December 2008 05:33  |
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 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 |
 |
Barbara Boehmer
Messages: 9104
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
