ORACLE HTTP & HTTPS 訪問

作者: 轻飘飘D | 来源:发表于2021-08-01 12:14 被阅读0次

1.1 普通 get 測試1

DECLARE
  req   UTL_HTTP.REQ;
  resp  UTL_HTTP.RESP;
  v_result VARCHAR2(1024);  
  v_url varchar2(50);
BEGIN 
  v_url:='http://192.168.52.43/myweb/test/ds?n=xag';   

  req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => 'GET');
  UTL_HTTP.SET_BODY_CHARSET('UTF-8');
  resp := UTL_HTTP.GET_RESPONSE(req);
  LOOP
    UTL_HTTP.READ_LINE(resp, v_result, TRUE);
    DBMS_OUTPUT.PUT_LINE(v_result);
  END LOOP;
  UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(resp);
END;

--輸出： Hello xag From Dubbo2Service

1.2 普通 get 測試2

declare 
v_req utl_http.req;v_resp utl_http.resp;
v_parts utl_http_multipart.parts := utl_http_multipart.parts();
v_result VARCHAR2(1024);v_url varchar2(50);v_n varchar2(100);
begin  
 v_n:='xag';
 v_url:='http://192.168.52.43/myweb/test/hs'; 
    
 utl_http_multipart.add_param(v_parts, 'n',v_n);
 v_req := utl_http.begin_request(v_url, 'GET', 'HTTP/1.1');
 utl_http_multipart.send(v_req, v_parts);
 v_resp := utl_http.get_response(v_req);
 dbms_output.put_line(v_resp.status_code);
 LOOP
    UTL_HTTP.READ_LINE(v_resp, v_result, TRUE);
    DBMS_OUTPUT.PUT_LINE(v_result);
  END LOOP;
  UTL_HTTP.END_RESPONSE(v_resp);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(v_resp); 
end;

--輸出：200
--      Hello xag From HessianService

2.1 https ACL環境準備（以下腳本 SYS 登錄執行）

#清除 acl_myweb
callDBMS_NETWORK_ACL_ADMIN.drop_acl('acl_myweb');

#創建 acl_myweb
begin
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl=> 'acl_myweb',description => 'acl myweb',principal=> 'MON',is_grant=> true,privilege =>'connect');
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl=>'acl_myweb',principal => 'MON',is_grant  => true,privilege => 'resolve');
end; 

#增加主機到 acl_myweb 中
begin
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'acl_myweb',host => '192.168.52.43');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'acl_myweb',host => '192.168.52.44');
end;

#授權訪問 UTL_HTTP
grant execute on UTL_HTTP to MON;

Select t.acl,t.principal,t.privilege,t.is_grant,t.invert 
From dba_network_acl_privileges t where t.acl=upper('acl_myweb');
------------------------------------------------------------------------------------
    ACL PRINCIPAL   PRIVILEGE   IS_GRANT    INVERT
1   ACL_MYWEB   MON resolve true    false
2   ACL_MYWEB   MON connect true    false

select * from dba_network_acls t where t.acl=upper('acl_myweb');
------------------------------------------------------------------------------------
   HOST         LOWER_PORT  UPPER_PORT  ACL     ACLID               ACL_OWNER
192.168.52.43                         ACL_MYWEB 00000000800027B0    SYS
192.168.52.44                         ACL_MYWEB 00000000800027B0    SYS

2.2 https 證書環境準備

[oracle@ORACLE122 ~]$ mkdir /home/oracle/wallet
[oracle@ORACLE122 ~]$ mkdir /home/oracle/cer

#1通過瀏覽器 https方式訪問網站，導出網站的根證書（base64 格式） xagca.cer
#2將xagca.cer 複製到 oracle 所在服務器的  /home/orace/cer/ 目錄下
#3 清理
orapki wallet remove -wallet /home/oracle/wallet -trusted_cert_all -pwd Xag##123
#4初始化目錄（僅首次需要）
orapki wallet create -wallet /home/oracle/wallet -pwd Xag##123 -auto_login
#5 顯示證書列表（暫無，因未加證書進入）
orapki wallet display -wallet /home/oracle/wallet

#6 新加證書到 wallet 中（後續有其他證書可再次追加）
orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert /home/oracle/cer/xagca.cer -pwd Xag##123

[oracle@ORACLE122 ~]$ orapki wallet display -wallet /home/oracle/wallet
Subject: EMAIL=xuaiguo@163.com,CN=xagca,OU=IT,O=XagCompany,L=zhuhai,ST=GuangDong,C=CN

2.3 普通 get https 測試

declare 
v_req utl_http.req;v_resp utl_http.resp;
v_parts utl_http_multipart.parts := utl_http_multipart.parts();
v_result VARCHAR2(1024);v_url varchar2(50);v_n varchar2(100);
l_wallet_path VARCHAR2(128);l_wallet_password VARCHAR2(128);
begin  
 l_wallet_path := 'file:/home/oracle/wallet';
 l_wallet_password := 'Xag##123';
 utl_http.set_wallet(l_wallet_path, l_wallet_password);
 
 v_n:='xag';
 v_url:='https://192.168.52.43/myweb/test/hs'; 
    
 utl_http_multipart.add_param(v_parts, 'n',v_n);
 v_req := utl_http.begin_request(v_url, 'GET', 'HTTP/1.1');
 utl_http_multipart.send(v_req, v_parts);
 v_resp := utl_http.get_response(v_req);
 dbms_output.put_line(v_resp.status_code);
 LOOP
    UTL_HTTP.READ_LINE(v_resp, v_result, TRUE);
    DBMS_OUTPUT.PUT_LINE(v_result);
  END LOOP;
  UTL_HTTP.END_RESPONSE(v_resp);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(v_resp); 
end;

3.1 普通post https測試1

DECLARE
  req   UTL_HTTP.REQ;resp UTL_HTTP.RESP;v_result VARCHAR2(1024);  
  v_chatid varchar2(100);v_msg varchar2(4000);v_url varchar2(50);
  v_paramer varchar2(4000);l_wallet_path VARCHAR2(128);l_wallet_password VARCHAR2(128);
begin  
  l_wallet_path := 'file:/home/oracle/wallet';
  l_wallet_password := 'Xag##123';
  utl_http.set_wallet(l_wallet_path, l_wallet_password);
 
  v_url:='https://192.168.52.43/myweb/send/msg';   
  v_chatid:='chat23a91fa8ea51b8b26d1f7bcde7afc76b';  
  v_msg:='測試:' || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
  
  v_paramer:='chatid=' || v_chatid || chr(38) || 'msg=' || v_msg; 

  req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => 'POST');
  UTL_HTTP.SET_BODY_CHARSET('UTF-8');
  UTL_HTTP.SET_HEADER (r=>  req,name=>  'Content-Type',value=>  'application/x-www-form-urlencoded');
  UTL_HTTP.SET_HEADER (r=>  req,name=>   'Content-Length',value=>   LENGTHB(v_paramer));
  UTL_HTTP.WRITE_RAW (r=> req,data=>UTL_RAW.CAST_TO_RAW(v_paramer)); 
  resp := UTL_HTTP.GET_RESPONSE(req);
  LOOP
    UTL_HTTP.READ_LINE(resp, v_result, TRUE);
    DBMS_OUTPUT.PUT_LINE(v_result);
  END LOOP;
  UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(resp);
END;

3.2 普通post https 測試 2

declare v_req utl_http.req;v_resp utl_http.resp;
v_parts utl_http_multipart.parts := utl_http_multipart.parts();
v_result VARCHAR2(1024);v_url varchar2(50);v_chatid varchar2(100);
v_msg varchar2(4000);l_wallet_path VARCHAR2(128);l_wallet_password VARCHAR2(128);
begin  
 l_wallet_path := 'file:/home/oracle/wallet';
 l_wallet_password := 'Xag##123';
 utl_http.set_wallet(l_wallet_path, l_wallet_password);
   
 v_url:='https://192.168.52.43/myweb/send/msg';   
 v_chatid:='chat23a91fa8ea51b8b26d1f7bcde7afc76b';  
 v_msg:='測試:' || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
  
 utl_http_multipart.add_param(v_parts, 'chatid',v_chatid);
 utl_http_multipart.add_param(v_parts, 'msg',v_msg);
 v_req := utl_http.begin_request(v_url, 'POST', 'HTTP/1.1');
 utl_http_multipart.send(v_req, v_parts);
 v_resp := utl_http.get_response(v_req);
 --dbms_output.put_line(v_resp.status_code);
 LOOP
    UTL_HTTP.READ_LINE(v_resp, v_result, TRUE);
    DBMS_OUTPUT.PUT_LINE(v_result);
  END LOOP;
  UTL_HTTP.END_RESPONSE(v_resp);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(v_resp); 
end;

普通 https 文件測試

declare v_req utl_http.req;v_resp utl_http.resp;
v_parts utl_http_multipart.parts := utl_http_multipart.parts();
v_file blob;v_result VARCHAR2(1024);v_url varchar2(50);
v_chatid varchar2(100);v_filename varchar2(50);
l_wallet_path VARCHAR2(128);l_wallet_password VARCHAR2(128);
begin  
 l_wallet_path := 'file:/home/oracle/wallet';
 l_wallet_password := 'Xag##123';
 utl_http.set_wallet(l_wallet_path, l_wallet_password);
   
 v_url:='https://192.168.52.43/myweb/send/file'; 
 v_chatid:='chat23a91fa8ea51b8b26d1f7bcde7afc76b';  
 as_xlsx.query2sheet('select t.table_name,t.num_rows from tabs t where t.num_rows>0 order by t.num_rows desc',p_start_rownum => 3 );
 v_file:=as_xlsx.finish();
 
 v_filename:='tabs_num_rows.xlsx';
 
 utl_http_multipart.add_param(v_parts, 'chatid',v_chatid);
 utl_http_multipart.add_file(v_parts, 'file', v_filename, 'application/pdf', v_file);
 v_req := utl_http.begin_request(v_url, 'POST', 'HTTP/1.1');
 utl_http_multipart.send(v_req, v_parts);
 v_resp := utl_http.get_response(v_req);

 LOOP
    UTL_HTTP.READ_LINE(v_resp, v_result, TRUE);
    DBMS_OUTPUT.PUT_LINE(v_result);
  END LOOP;
  UTL_HTTP.END_RESPONSE(v_resp);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(v_resp); 
end;

网友评论

本文标题：ORACLE HTTP & HTTPS 訪問

本文链接：https://www.haomeiwen.com/subject/hifypltx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

ORACLE HTTP & HTTPS 訪問

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读