oracle 发送 HTTP 请求

包体

create or replace package ODK_HTTP is

  procedure p_PostRequest(v_url    in varchar2,
                          v_params in varchar2,
                          retData  out clob,
                          retCode  out varchar2,
                          retMsg   out varchar2);

  procedure p_GET_DETAIL(v_params in varchar2,
                         retData  out clob,
                         retCode  out varchar2,
                         retMsg   out varchar2);

  -- HTTP请求：明细信息下载
  procedure p_HTTP_TEST(v_url    in varchar2,
                        v_params in varchar2,
                        retData  out varchar2,
                        retCode  out varchar2,
                        retMsg   out varchar2);

end ODK_HTTP;

create or replace package body ODK_HTTP is

  --
  procedure p_PostRequest(v_url    in varchar2,
                          v_params in varchar2,
                          retData  out clob,
                          retCode  out varchar2,
                          retMsg   out varchar2) is
    http_req  UTL_HTTP.REQ;
    http_resp UTL_HTTP.RESP;
    v_FUrl    varchar2(4000);
  begin
    retCode := '1';
    retMsg  := '调用服务成功';
    -- 载入传入参数
    v_FUrl := v_Url ||'?'|| v_params;

    http_req := UTL_HTTP.BEGIN_REQUEST(url => v_FUrl, method => 'POST');
    UTL_HTTP.SET_BODY_CHARSET('UTF-8');
    UTL_HTTP.SET_HEADER(r     => http_req,
                        name  => 'Content-Type',
                        value => 'application/x-www-form-urlencoded');
    --UTL_HTTP.SET_HEADER(http_req, 'Keep-Alive', '  timeout=1');

    UTL_HTTP.WRITE_RAW(r    => http_req,
                       data => UTL_RAW.CAST_TO_RAW(v_params));

    http_resp := UTL_HTTP.GET_RESPONSE(http_req);
    LOOP
      UTL_HTTP.READ_LINE(http_resp, retData, TRUE);
    END LOOP;
    UTL_HTTP.END_RESPONSE(http_resp);
  EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY THEN
      UTL_HTTP.END_RESPONSE(http_resp);
    WHEN OTHERS THEN
      retCode := '0';
      retMsg  := '调用服务异常:(' || sqlcode || ')' || sqlerrm;
  end;

  -- HTTP请求：明细信息下载
  procedure p_GET_DETAIL(v_params in varchar2,
                         retData  out clob,
                         retCode  out varchar2,
                         retMsg   out varchar2) is
    v_url varchar2(1000);
  begin
    -- 获取服务的url
    select aaa005
      into v_url
      from aa01
     where aaa001 = 'DOWNLOAD_DETAIL_HTTP_URL';
    p_PostRequest(v_url, v_params, retData, retCode, retMsg);
  end;

  -- HTTP请求：明细信息下载
  procedure p_HTTP_TEST(v_url    in varchar2,
                        v_params in varchar2,
                        retData  out varchar2,
                        retCode  out varchar2,
                        retMsg   out varchar2) is
  begin
    p_PostRequest(v_url, v_params, retData, retCode, retMsg);
  end;

end ODK_HTTP;

调用例子

declare
  retdata clob;
  retcode varchar2(1000);
  retmsg  varchar2(1000);
  par     varchar2(1000);
begin
  par := 'reqNum=QUERY_ROAD_002' || chr(38) ||
         'jsonData=%7B"AAC001"%3A"0000000002"%2C"AAC002"%3A""%2C"QUERY_ID"%3A"Q006"%7D' ||
         chr(38) || 'userName=xxm' || chr(38) || 'businessType=00' ||
         chr(38) || 'businessDate=20180928202533';
  odk_http.p_postrequest('http://10.83.143.52:8091/sscmApi/httpService/doRequest',
                         par,
                         retdata,
                         retcode,
                         retmsg);
  dbms_output.put_line(retdata);
  dbms_output.put_line(retcode);
  dbms_output.put_line(retmsg);
end;

设置 NETWORK_ACL

BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL 
(acl          => 'xxm_20180928.xml',
description  => 'Enables network permissions for the xxm_20180928 server',
principal    => 'INSIIS6',
is_grant     => TRUE,
privilege    => 'connect');
END;

BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL 
(acl         => 'xxm_20180928.xml',
host        => '10.83.143.52',
lower_port  => 8091,
upper_port  => 8091);
COMMIT;
END;

BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE 
( acl => 'xxm_20180928.xml',
principal => 'INSIIS6',
is_grant => TRUE, 
privilege => 'connect', 
start_date => null, 
end_date => null); 
end;

-- 查询
SELECT host, lower_port, upper_port, acl FROM dba_network_acls;