oracle执行java代码

项目里，经常用到oracle里编写 java 类，调用存储过程，于是这篇文档闪亮登场！
具体步骤：

1、登陆某一用户登录，并创建java程序资源，在pl/sql中java source中显示你所编写的java代码；
create or replace and compile java source named bb_wx_replosssbk as
import java.sql.*;
import oracle.jdbc.driver.*;

public class bb_wx_replosssbk
{
    /**
    *   社保卡挂失
    */
    public static String callProc(String sSfzh,String sPwd,String sType)
    {
        OracleDriver driver = new OracleDriver();
        Connection connection=null;
        CallableStatement cstmt=null;
        String sRtn = "beg";
        try
        {
            sRtn = " try beg";
            connection = DriverManager.getConnection("jdbc:oracle:thin:user/pwd@127.0.0.1:1521:orcl");
            sRtn = "con";
            cstmt = connection.prepareCall("{call run_replosssbk(?,?,?,?)}");
            sRtn = "invoke";
            cstmt.setString(1, sSfzh);
            cstmt.setString(2, sPwd);
            cstmt.setString(3, sType);
            cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
            sRtn = "set value";
            cstmt.executeUpdate();
            sRtn = "execute";
            sRtn = cstmt.getString(4);
        }catch (Exception e) {
            sRtn = e.toString();
            e.printStackTrace();
        }finally {
            try {
                if (cstmt != null) {
                  cstmt.close();
                }
                if (connection != null) {
                    connection.close();
                }
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
        return sRtn;
    }
}

2、创建调用Java资源的函数
create or replace function run_bb_wx_replosssbk(
sSfz in varchar2,
sPwd in varchar2,
sType in varchar2
)
return varchar2
as
language java name 'bb_wx_replosssbk.callProc(java.lang.String,java.lang.String,java.lang.String) return java.lang.String';

3、建立一过程调用存储过程
create or replace procedure RUN(
sSfz in varchar2,
sPwd in varchar2,
sType in varchar2
sRtn out varchar2
)
as
begin
--sRtn := run_bb_wx_replosssbk(sSfz in varchar2,sPwd in varchar2,sType in varchar2);
Select run_bb_wx_replosssbk(sSfz in varchar2,sPwd in varchar2,sType in varchar2)
Into sRtn from dual;
end;
/

呵呵，到此为止，基本步骤已经完成，如果你觉得现在可以顺利调用，孩子，醒醒吧，还有权限问题！！
网上抄了下面一段
begin
Dbms_Java.Grant_Permission('new','java.io.FilePermission', '<<ALL FILE>>','read ,write, execute, delete');
Dbms_java.grant_permission('new', 'SYS:java.io.FilePermission', '<<ALL FILES>>','read ,write, execute, delete');
Dbms_Java.Grant_Permission('new', 'java.io.FilePermission', 'd:a.bat','read ,write, execute, delete');
dbms_java.grant_permission('new', 'java.lang.RuntimePermission','*','writeFileDescriptor' );
end;
很神奇，上面一段不行！！不知道为啥……
还是用最简单的方法，用有管理员权限的用户登录pl/sql ，选择users，找到new用户，edit一下，选择Role privileges，加上javasyspriv权限和javauserpriv权限。
OK，到此为止，所有步骤已经完成，可以正常使用了，O(∩_∩)O哈哈~

评论

1 楼 mao_123 2016-09-23  

oracle函数直接调java类方法，长见识了