ORACLE HTTP & HTTPS 訪問
2021-08-01 本文已影响0人
轻飘飘D
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;
