Oracle调用shell脚本方法（二）

Oracle 用scheduler调用外部脚本方法：

http://blog.youkuaiyun.com/xile99/article/details/15504219

  

      下面利用在Oracle里创建Java class和function来实现，这种方法可以获取外部脚本执行的返回值：

       首先在Oracle上创建如下的Java class：

CREATEOR REPLACEAND COMPILEJAVA SOURCENAMED "OSCommand"AS

import java.io.BufferedReader;

import java.io.IOException;

import java.io.InputStreamReader;

 

public class OSCommand{                                                        

public static String Run(String Command){                                    

 StringBuffer sb =new StringBuffer();

   try {  

           Process p = Runtime.getRuntime().exec("Command "); 

           BufferedReader in =new BufferedReader(  

                               new InputStreamReader(p.getInputStream()));  

           String line =null;  

           while ((line = in.readLine()) !=null) {  

               System.out.println(line);

               sb.append(line);

           }  

       } catch (IOException e) {  

           e.printStackTrace();  

       }

       return sb.toString();

}

}

    创建function：

CREATE or REPLACE FUNCTION OSCommand_Run(CommandINSTRING)

RETURNVARCHAR2IS

LANGUAGEJAVA

NAME'OSCommand.Run(java.lang.String) return int';

/

     以sys登陆数据库，并为用户scott赋权：

SQL> conn sys/oracle as sysdba

Connected.

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

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

execute dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'readFileDescriptor', '*' );

 

   调用function来运行外部脚本：

SQL> select OSCommand_Run('cmd.exe /C echo asdgasdfg') from dual;

OSCOMMAND_RUN3('CMD.EXE/CECHOASDGASDFG')
-------------------------------------------------------------------------
asdgasdfg

  这里跑的是windows操作系统下命令行的简单命令，看到可以返回值。

  在linux环境的测试，未完待续....