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;  

测试结果

pljson数据解析见

pljson获取Json类型数据的内容 - STR少寒 - 博客园 (cnblogs.com)

pljson获取Json类型数据的内容_STR少寒的博客-CSDN博客