利用utl_http在oracle数据库调用webservice

--用oracle的utl_http调用，oracle11g需要建ACL
--可参考http://log-cd.iteye.com/blog/1345093和http://blog.sina.com.cn/s/blog_952ddba50102wung.html两篇文章，https://www.cnblogs.com/huanghongbo/p/7145365.html

/*创建ACL
用具有管理员权限的用户进入数据库，执行以下脚本创建ACL。
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl =>'test_acl_file.xml', --控制的xml
description => 'A test of theACL functionality',
principal => 'TEST1',--用户
is_grant => TRUE, --是否可以访问
privilege => 'connect',--设置权限
start_date => SYSTIMESTAMP,
end_date => NULL);

COMMIT;
END;

ACL分配网络
ACL分配网络，执行以下脚本分配网络。
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'test_acl_file.xml',
host => 'xxx.xxx.xxx.xxx', --acl可以访问的网络
lower_port => xx,
upper_port => NULL);
COMMIT;
END;

查看ACL
如果访问的网络数量比较多，可以执行以下脚本，就可以访问所有网络。
CONN / AS SYSDBA
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'open_acl_file.xml',
description => 'A test of the ACL functionality',
principal => 'TEST',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);

DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'open_acl_file.xml',
host => '*',
lower_port => 1,
upper_port => 9999);

COMMIT;
END;

建好ACL和分配网络后可以通过下面的方式查看。
SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;

SELECT host, lower_port, upper_port, privilege, status
FROM user_network_acl_privileges;

--添加用户到ACL控制访问列表
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege ( -- 添加访问权限列表项
acl => 'open_acl_file.xml', -- 刚才创建的acl名称
principal => 'ITS', -- 授权或取消授权用户
is_grant => TRUE, -- TRUE授权，FALSE取消授权
privilege => 'connect', -- 权限列表
start_date => SYSTIMESTAMP,
end_date => null);
COMMIT;
END;

--删除ACL配置文件
BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl(
'open_acl_file.xml'
);
COMMIT;
END;

--删除ACL中用户权限
BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege(
'open_acl_file.xml', 'ITS', NULL, 'connect'
);
COMMIT;
END;

*/

 

function f_webcall_java
(
as_urlstr varchar2 ,
as_paramValue clob,
as_charset varchar2
)
RETURN varchar2 ;

 

procedure scm_webcall_service
(
as_paramType in varchar2,
as_paramValue in clob,
as_sevtype in varchar2,
ai_rtd out number,
as_rmsg out varchar2

);

 

 

/*
1 名称 scm_webcall_service
2 功能 CMS调用外部服务连接
3 伪代码
4 修改记录

5 说明
5.1
6 存在问题
7 需完善的环节
*/
procedure scm_webcall_service
(
as_paramType in varchar2,
as_paramValue in clob,
as_sevtype in varchar2,
ai_rtd out number,
as_rmsg out varchar2
)
is
--1标准参数定义-----------------------------------------------------------------------------
p_object_name constant varchar2(200) := SYS_PUBLIC.F_SYS_GET_OBJNAME()||'.scm_webcall_service.';
ls_step varchar2(100);

--2其它参数定义---------------------
env clob;
http_req utl_http.req;
http_resp utl_http.resp;
return_value xmltype;
error_value xmltype;
error_code VARCHAR(256);
error_string VARCHAR2(32767);

ls_result varchar2(32767);
ls_begin_request varchar2(250);
ls_webserviceaddress pub_websevaddress.address%type;
ls_charset pub_websevaddress.charset%type;
ls_caltype pub_websevaddress.caltype%type;

--3游标定义-------------------------
begin
/*
1 参数校验
*/
ls_step := '1. 参数校验';
if nvl(as_paramType, '!@#') = '!@#' then
as_rmsg := '参数类型不能为空！';
goto ERRORPRM;
end if;

if nvl(as_paramValue, '!@#') = '!@#' then
as_rmsg := '参数值不能为空！';
goto ERRORPRM;
end if;

 

ls_step := '1.1 获取webservice地址';
begin
select pw.address,pw.charset,pw.caltype
into ls_webserviceaddress,ls_charset,ls_caltype
from pub_websevaddress pw,
pub_company pc
where pw.syscode = pc.systype
and pw.stopflag = '00'
and pw.sevcode = upper(substr(as_paramType,1,3))
;
if sql%rowcount = 0 then
as_Rmsg := '更新确认单转码操作序列，没有找到记录';
goto ERRORPRM;
end if;
EXCEPTION
WHEN OTHERS THEN
as_Rmsg := '更新确认单操作序列错:' || sqlerrm;
goto ERRORPRM;
end;

IF ls_caltype NOT IN('00','10') THEN
as_Rmsg := '调用方法配置不正确:' || ls_caltype;
goto ERRORPRM;
END IF;

IF ls_charset IS NULL THEN
ls_charset :='gb2312';
END IF;

/*
2 执行webservice
*/
ls_step := '2. 执行webservice';
IF ls_caltype = '10' THEN
ls_step := '2.1. 执行webservice的JAVA调用';
env :='<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ws="http://www.sinopharmsoft.com/ws">
<soapenv:Header/>
<soapenv:Body>
<ws:invoke>
<paramType>' || as_paramType || '</paramType>
<paramString>' || as_paramValue || '</paramString>
</ws:invoke>
</soapenv:Body>
</soapenv:Envelope>';

--执行JAVA调用
ls_result :=f_webcall_java
(
as_urlstr =>ls_webserviceaddress ,
as_paramValue =>env,
as_charset =>ls_charset
) ;
as_rmsg := replace(ls_result, '&quot;', '"');
if instr(as_rmsg,'"execFlag":-1') >0 then
goto errorprm;
end if;

ELSE
ls_step := '2.2. 执行webservice的ORACLE调用';
begin
env :='<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ws="http://www.sinopharmsoft.com/ws">
<soapenv:Header/>
<soapenv:Body>
<ws:invoke>
<paramType>' || as_paramType || '</paramType>
<paramString>' || as_paramValue || '</paramString>
</ws:invoke>
</soapenv:Body>
</soapenv:Envelope>';

env := utl_url.escape(env,true,'gb2312');

ls_begin_request := ls_webserviceaddress;

http_req := utl_http.begin_request(ls_begin_request, 'POST', 'HTTP/1.0');
utl_http.set_header(http_req, 'Content-Type', 'text/xml;charset=gb2312');
utl_http.set_header(http_req, 'Content-Length', length(env));
utl_http.set_header(http_req, 'SOAPAction', '');

utl_http.write_text(http_req, utl_url.unescape(env,'gb2312'));

http_resp := utl_http.get_response(http_req);

utl_http.read_text(http_resp, env);
utl_http.end_response(http_resp);

return_value := xmltype.createxml(env).extract('/soap:Envelope/soap:Body/child::node()','xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"');
error_value := return_value.extract('/soap:Fault','xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"');

if (error_value is not null) THEN
error_string := error_value.extract('/soap:Fault/faultstring/child::text()','xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"').getstringval();
error_code := error_value.extract('/soap:Fault/faultcode/child::text()','xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"').getstringval();
raise_application_error(-20000,'error in authentification: ' || error_string ||' - '|| error_code);
end if;

ls_result := return_value.getStringVal();
ls_result := replace(ls_result, '&quot;', '"');
as_rmsg := ls_result;

if instr(ls_result,'"execFlag":-1') >0 then
goto errorprm;
end if;

exception
when utl_http.end_of_body then
utl_http.end_response(http_resp);
as_Rmsg := '执行webservice出错,'|| sqlerrm;
goto errorprm;
when others then
utl_http.end_response(http_resp);
utl_http.end_request(http_req);
as_Rmsg := '执行webservice出错,'|| sqlerrm;
goto errorprm;
end;
END IF;
------------------------------------------
<<endprm>>
if nvl(ai_rtd,1)<0 then
ai_rtd := 1;
end if;
ai_rtd := nvl(ai_rtd,1);
as_rmsg := p_object_name||'存储过程成功操作，webservice返回内容：'||ls_result||'!' ;
return ;
<<errorprm>>
rollback;
if nvl(ai_rtd,-1)>0 then
ai_rtd := -1;
end if;
ai_rtd := nvl(ai_rtd,-1);
as_rmsg := p_object_name||ls_step||':'||chr(13)||SYS_PUBLIC.F_SYS_GET_MESSPETEXT()||as_rmsg ;
return ;
------------------------------------------------------
exception
when others then
ai_rtd := -1;
as_rmsg := p_object_name||ls_step||':'||chr(13)||SYS_PUBLIC.F_SYS_GET_MESSPETEXT()||as_rmsg ||sqlerrm(sqlcode);
rollback;
return ;
end;

 

 

 

 

 使用JAVA方式调用前需要先在数据库给当前用户授权，授权语句如下：登陆SYS用户授权（修改实际的数据库用户和webservice访问服务地址）
SQL> exec dbms_java.grant_permission( 'CMSDTPDEV', 'SYS:java.net.SocketPermission', 'xxx.xxx.xxx.xxx:xxx', 'connect,resolve' ); 

 

 

由于上面的数据库直接调webservice，参数超过4000会报错，所以需要使用数据库中写java程序去转一下：

create or replace and compile java source named sys_java_webservice as
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import oracle.sql.CLOB;
import java.sql.SQLException;
import java.util.Scanner;
/*
1 名称 sys_webservice
2 功能 JAVA调用webservice
3 伪代码
4 修改记录
1 bg 2018-09-26 新建
5 说明
6 存在问题
7 需完善的环节
*/
public class sys_java_webservice {

public static String consume(String as_urlstr, Object as_paramValue, String as_charset) throws Exception{
if (as_urlstr==null || as_urlstr.length()==0) {
return "webser服务地址不能为空";
}
URL wsUrl;
try {
wsUrl = new URL(as_urlstr);
} catch (MalformedURLException e) {
return "webser服务地址或者格式错误";
}
URLConnection wsUrlConnection;
try {
wsUrlConnection = wsUrl.openConnection();
} catch (IOException e) {
return "ERROR: wsUrl.openConnection() -- IOException";
}
wsUrlConnection.setRequestProperty("Content-Type",
as_charset == null ? "text/xml;charset=gb2312" : "text/xml;charset=" + as_charset);
wsUrlConnection.setRequestProperty("Connection", "Keep-Alive");
wsUrlConnection.setRequestProperty("SOAPAction", "");
if (as_paramValue == null) {

return "传入参数as_paramValue为空";
}
CLOB msgClob = (CLOB)as_paramValue;
long length;
try {
length = msgClob.length();
if (length <= 1) {
return "传入参数内容为空";
}
} catch (SQLException e) {
return "ERROR: msgClob.length()";
}
String msgStr = msgClob.stringValue();
wsUrlConnection.setRequestProperty("Content-Length", String.valueOf(msgStr.length()));
wsUrlConnection.setDoOutput(true);
wsUrlConnection.setDoInput(true);
OutputStream outputStream;
try {
outputStream = wsUrlConnection.getOutputStream();
} catch (IOException e) {
return "ERROR: wsUrlConnection.getOutputStream()";
}
PrintWriter printWriter = new PrintWriter(outputStream);
printWriter.write(msgStr);
printWriter.close();
try {
outputStream.close();
} catch (IOException e) {
return "ERROR: outputStream.close()";
}

InputStream inputStream;
try {
inputStream = wsUrlConnection.getInputStream();
} catch (IOException e) {

return "ERROR: wsUrlConnection.getInputStream()";
}
Scanner scanner = new Scanner(inputStream);
StringBuffer stringBuffer = new StringBuffer();
while (scanner.hasNextLine()) {
String nextLine = scanner.nextLine();
stringBuffer.append(nextLine);
}
scanner.close();
try {
inputStream.close();
} catch (IOException e) {
return "ERROR: inputStream.close()";
}

return stringBuffer.toString();
}
};

 

 

create or replace package body scm_webservice is
/*
1 名称 f_webcall_java
2 功能 JAVA调用webservice
3 伪代码
4 修改记录
1 bg 2018-09-26 新建
5 说明
6 存在问题
7 需完善的环节
*/
function f_webcall_java
(
as_urlstr varchar2 , --地址
as_paramValue clob, --内容,XML格式
as_charset varchar2 --JAVA字符集
)
RETURN varchar2 -- 返回值的数据类型
AS language java name 'sys_java_webservice.consume(java.lang.String, java.lang.Object, java.lang.String) return java.lang.String';

转载于:https://www.cnblogs.com/ydjs/p/9145525.html