Oracle 发送HTTP请求及封装成包
Oracle 发送HTTP请求及封装成包
- 查询是否为当前用户分配acl权限
select * from dba_network_acl_privileges ;
- 查看文件夹下是否有该acl文件
SELECT any_path FROM resource_view WHERE any_path like '/sys/acls/%.xml';
- 如果有，执行以下sql，将系统中已经有的acl分配给当前用户
begin
dbms_network_acl_admin.add_privilege
(acl => '/sys/acls/utl_http.xml', -- 命名ACL
principal => 'SONG', -- 用户
is_grant => true, -- true表示赋权，false表示取消赋权
privilege => 'connect'); -- 权限限制
end;
- 如果没有acl文件，则需要创建ac
begin
dbms_network_acl_admin.create_acl
(acl => '/sys/acls/utl_http.xml', -- 命名
description => 'url http 请求', -- 描述
principal => 'SONG', -- 要赋权限的用户
is_grant => true, -- true表示赋权，false表示取消赋权
privilege => 'connect'); -- 权限限制
end;
- 分配地址端口
begin
dbms_network_acl_admin.assign_acl
(acl => '/sys/acls/utl_http.xml', -- 命名
host => '*', -- 服务器地址
lower_port => 1, -- 端口从
upper_port => 10000); -- 端口到
end;
- 删除acl配置文件
begin
dbms_network_acl_admin.drop_acl(
'/sys/acls/utl_http.xml'
);
end;
创建POST和Get函数
- Get方式：
CREATE OR REPLACE FUNCTION FN_HTTP_GET (v_url VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
v_line VARCHAR2 ( 4000 );
v_text VARCHAR2 ( 4000 );
BEGIN
v_text := '';
BEGIN
req := UTL_HTTP.BEGIN_REQUEST ( url => v_url, method => 'GET' );
UTL_HTTP.SET_BODY_CHARSET('UTF-8');
UTL_HTTP.SET_HEADER(req, 'Content-Type', 'application/x-www-form-urlencoded');
resp := UTL_HTTP.GET_RESPONSE ( req );
LOOP
UTL_HTTP.READ_LINE ( resp, v_line, TRUE );
v_text := v_text || v_line;
END LOOP;
UTL_HTTP.END_RESPONSE( resp );
UTL_HTTP.END_REQUEST( req );
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE ( resp );
WHEN OTHERS THEN
UTL_HTTP.END_RESPONSE(resp);
UTL_HTTP.END_REQUEST(req);
END;
return v_text;
END;
END;
- Post方式：
CREATE OR REPLACE FUNCTION FN_HTTP_POST (v_url VARCHAR2, v_body VARCHAR2, v_body_type VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
v_line VARCHAR2 ( 4000 );
v_text VARCHAR2 ( 4000 );
BEGIN
v_text := '';
BEGIN
req := UTL_HTTP.BEGIN_REQUEST ( url => v_url, method => 'POST' );
UTL_HTTP.SET_BODY_CHARSET('UTF-8');
UTL_HTTP.SET_HEADER(req, 'Content-Type', v_body_type);
utl_http.set_header(req, 'Content-Length',lengthb(v_body));
utl_http.write_text(req, v_body);
resp := UTL_HTTP.GET_RESPONSE ( req );
LOOP
UTL_HTTP.READ_LINE ( resp, v_line, TRUE );
v_text := v_text || v_line;
END LOOP;
UTL_HTTP.END_RESPONSE( resp );
UTL_HTTP.END_REQUEST( req );
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE ( resp );
WHEN OTHERS THEN
UTL_HTTP.END_RESPONSE(resp);
UTL_HTTP.END_REQUEST(req);
END;
return v_text;
END;
END;
使用pljson获取接口调用的值
-
下载安装plsql
- 解析请求结果
DECLARE
test VARCHAR2(2000);
v_json pljson;
begin
v_json := pljson(fn_http_get('http://xxxx.com/Pxyb/CheckInfo?cardID=123')) ;
dbms_output.put_line(v_json.to_char);
dbms_output.put_line(pljson_ext.get_string(v_json, 'InfoMsg'));
end;
参考博客连接
Oracle 使用UTL_HTTP发送http请求_tmaczt的博客-CSDN博客
Oracle:网络访问被访问控制列表 (ACL) 拒绝 - hziwei - 博客园 (cnblogs.com)
封装成包
创建包
create or replace package PAK_YBHttp is
procedure GetPatientInfo(cardID In Varchar2,Json_out out pljson);
end PAK_YBHttp;
创建包体
create or replace package body PAK_YBHttp
is
procedure GetPatientInfo(cardID In Varchar2,Json_out out pljson) as
Err_Item Exception;
begin
Json_out := pljson(fn_http_get('http://xxxx.com/Pxyb/CheckInfo?cardID='||cardID)) ;
Exception
when Err_Item then
dbms_output.put_line('error stack at top level');
dbms_output.put_line(dbms_utility.format_error_backtrace);
dbms_output.put_line(dbms_utility.format_error_stack);
When Others Then
dbms_output.put_line('error stack at top level');
dbms_output.put_line(dbms_utility.format_error_backtrace);
dbms_output.put_line(dbms_utility.format_error_stack);
end GetPatientInfo;
end PAK_YBHttp;
包体调用
DECLARE
cardID varchar2(1000);
Json_out pljson;
baseInfo pljson;
begin
cardID := '123' ;
pak_ybhttp.GetPatientInfo(cardID =>cardID,Json_out => Json_out);
dbms_output.put_line(Json_out.to_char);
baseInfo := pljson_ext.get_json(Json_out,'baseInfo');
dbms_output.put_line(baseInfo.to_char);
dbms_output.put_line(pljson_ext.get_string(Json_out,'InfoMsg'));
dbms_output.put_line(pljson_ext.get_string(Json_out,'baseInfo.MZ'));
dbms_output.put_line(case when pljson_ext.get_bool(Json_out, 'Success') then 'true' else 'false' end);
end;
测试结果
