1. 去oracle官网上下载dbws-callout-utility-10131.zip,地址为:https://oracle-base.com/articles/10g/utl_dbws-10g(注意请根据自己oracle的版本来选择下载对应的jar)。
2. 解压后将sqlj/lid里面的jar包和sql文件都放到oracle安装目录下的<oracle_install_dir>/sqlj/lib中。
3. 用管理员启动命令行,进入<oracle_install_dir>/sqlj/lib文件夹内,使用loadjava命令(一般安装完jdk或oracle之后就会有)将jar包导入oracle对应的用户中:
loadjava-u user/password@oracle -r -v -f -genmissing -s -grant public dbwsclientws.jardbwsclientdb102.jar
注意dbwsclientdb102.jar版本,根据oracle的版本选择文件夹中的的版本
user为用户名,password为登录密码,oracle为数据库实例,在使用该命令之前要给用户赋予CONNECT, RESOURCE and CREATE PUBLIC SYNONYM权限,执行完成提示成功后(一般出错都是权限不够),登录用户:
Sqlplususer/password
SQL> @utl_dbws_decl.sql
Package created.
SQL> @utl_dbws_body.sql
Package bodycreated.
Grant succeeded.
SQL> desc utl_dbws
PROCEDUREADD_PARAMETER
Argument Name TypeIn/Out Default?
----------------------------------------------------- ------ --------
CALL_HANDLE NUMBERIN
XML_NAME VARCHAR2IN
Q_NAMEVARCHAR2(4096) IN
4. 继续给该用户授予下列权限:
conn /as sysdba
Connected.
SQL> call dbms_java.grant_permission('user','SYS:java.lang.RuntimePermission','shutdownHooks', '' );
SQL> calldbms_java.grant_permission('user','SYS:java.util.logging.LoggingPermission','control', '' );
SQL> calldbms_java.grant_permission('user','SYS:java.util.PropertyPermission','http.proxySet','write');
SQL> calldbms_java.grant_permission('user','SYS:java.util.PropertyPermission','http.proxyHost','write');
SQL> calldbms_java.grant_permission('user','SYS:java.util.PropertyPermission','http.proxyPort','write');
SQL> calldbms_java.grant_permission('user','SYS:java.lang.RuntimePermission','getClassLoader','');
SQL> calldbms_java.grant_permission('user','SYS:java.net.SocketPermission','*','connect,resolve');
SQL> calldbms_java.grant_permission('user','SYS:java.util.PropertyPermission','*','read,write');
SQL> calldbms_java.grant_permission('user','SYS:java.lang.RuntimePermission','setFactory','');
SQL> calldbms_java.grant_permission('user','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');
SQL> call dbms_java.grant_permission('user','SYS:java.lang.RuntimePermission', 'createClassLoader', '' );
(user为用户名)
示例:
1..编写调用函数:
CREATE OR REPLACE FUNCTION getCompanyFpb (p_int_1 INVARCHAR2, p_int_2 INVARCHAR2)
RETURN VARCHAR2
AS
--定义变量
l_service UTL_DBWS.service;
l_call UTL_DBWS.call;
l_wsdl_url VARCHAR2(32767);
l_namespace VARCHAR2(32767);
l_service_qname UTL_DBWS.qname;
l_port_qname UTL_DBWS.qname;
l_operation_qname UTL_DBWS.qname;
l_xmltype_in SYS.XMLTYPE;
l_xmltype_out SYS.XMLTYPE;
l_return VARCHAR2(32767);
BEGIN
--web servide地址 wsdl必须加上
--官方提供的地址 http://oracle-base.com/webservices/server.php?wsdl
l_wsdl_url := 'http://oracle-base.com/webservices/server.php?wsdl';
--web service文档头部中属性有namespace的值
l_namespace := 'http://oracle-base.com/webservices/'’;
--web service中类名(可为空)
l_service_qname :=UTL_DBWS.to_qname(l_namespace,'Calculator');
--端口号文档中有port的属性值节点的name值(可为空)
l_port_qname :=UTL_DBWS.to_qname(l_namespace,'CalculatorPort');
--调用的方法名(必填)
l_operation_qname := UTL_DBWS.to_qname(l_namespace,'ws1');
l_service := UTL_DBWS.create_service ( wsdl_document_location => URIFACTORY.getURI(l_wsdl_url), service_name => l_service_qname);
l_call := UTL_DBWS.create_call (service_handle => l_service, port_name=> l_port_qname, operation_name => l_operation_qname);
--参数,用的是xml数据格式,需要拼装
l_xmltype_in := SYS.XMLTYPE('<?xmlversion="1.0" encoding="utf-8"?>
<ws_add xmlns="' || l_namespace || '">
<int1>' || p_int_1 || '</int1>
<int2>' || p_int_2 || '</int2>
</ws_add>');
--接收的数据
l_xmltype_out :=UTL_DBWS.invoke(call_Handle => l_call, request => l_xmltype_in);
UTL_DBWS.release_call (call_handle=> l_call);
UTL_DBWS.release_service(service_handle => l_service);
l_return :=l_xmltype_out.getStringVal();
RETURN l_return;
EXCEPTION
WHENOTHERSTHEN
RETURNsubstr(sqlerrm,0,2000);
END;
2.调用方法,要用授权过的用户才能调用
SQL> SELECT getCompanyFpb(‘p_int_1’,‘p_int_2’) from dual;
官方参考文档:https://oracle-base.com/articles/10g/utl_dbws-10g