设置 Oracle 12c R1 以连接到 Web 服务 [英] Set up Oracle 12c R1 to connect to web service

问题描述

我已经使用 DBMS_NETWORK_ACL_ADMIN 包设置了 ACL.在这里

I have set up ACL using DBMS_NETWORK_ACL_ADMIN package. Here it's

但是当我尝试使用 GET 连接到上述 Web 服务时，出现错误

But when I am trying to connect to the above web service using GET, I am getting error

ORA-29273: HTTP request failed
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 368
ORA-06512: at "SYS.UTL_HTTP", line 1118
ORA-06512: at "APEX_040200.WWV_FLOW_WEB_SERVICES", line 550
ORA-06512: at "APEX_040200.WWV_FLOW_WEBSERVICES_API", line 197
ORA-06512: at line 7
29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
           Fix the error and retry the HTTP request.

我还想提到主机 oracle-base.com 运行良好.下面的代码工作正常.

I would also like to mention host oracle-base.com is working perfectly. below code works fine.

declare
l_response CLOB;
l_url varchar2(4000) := 'http://oracle-base.com/webservices/add-numbers.php'; --web service URL
l_result NUMBER;
begin
---get XML from 
l_response := apex_web_service.make_rest_request(p_url => l_url,
                                                 p_http_method => 'GET',
                                                 p_parm_name  => apex_util.string_to_table('p_int_1:p_int_2'),
                                                 p_parm_value => apex_util.string_to_table('12:12')
                                                );


dbms_output.put_line(l_response);
--parse the response
l_result :=apex_web_service.parse_xml(p_xml => XMLTYPE(l_response), --convert CLOB to XML
                           p_xpath => '//number/text()'
                        );
dbms_output.put_line(l_result);
end;

但是当我尝试为 reqres.in 做同样的事情时，它失败了.我如何添加 reqres.in

but when I am tryig to do the same for reqres.in it's getting failed. How I added reqres.in

DECLARE
  l_principal VARCHAR2(20) := 'APEX_040200';
  --l_principal VARCHAR2(20) := 'APEX_050000';
  --l_principal VARCHAR2(20) := 'APEX_050100';
  --l_principal VARCHAR2(20) := 'APEX_180200';
BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'reqres.in', 
    lower_port => 80,
    upper_port => 80,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => l_principal,
                              principal_type => xs_acl.ptype_db)); 
END;
/

我要执行的命令:

declare
l_response CLOB;
l_url varchar2(4000) := 'https://reqres.in/api/users?page=2'; --web service URL
l_result NUMBER;
begin
---get XML from 
l_response := apex_web_service.make_rest_request(p_url => l_url,
                                                 p_http_method => 'GET'
--                                                 p_parm_name  => apex_util.string_to_table('p_int_1:p_int_2'),
--                                                 p_parm_value => apex_util.string_to_table('12:12')
                                                );


dbms_output.put_line(l_response);
----parse the response
--l_result :=apex_web_service.para(p_xml => XMLTYPE(l_response), --convert CLOB to XML
--                           p_xpath => '//number/text()'
--                        );
dbms_output.put_line(l_result);
--APEX_JSON.parse(l_result); -- APEX_JSON is not available

end;

推荐答案

要发出 HTTPS 请求，您需要做的第一件事是指定正确的端口.HTTPS 使用 443，而 HTTP 使用 80:

To make an HTTPS request, the first thing you need to do is to specify the correct port. HTTPS uses 443 whereas HTTP uses 80:

     DBMS_NETWORK_ACL_ADMIN.append_host_ace (
       host       => 'reqres.in', 
       lower_port => 443,
       upper_port => 443,
       ace        => xs$ace_type(....

这应该可以消除ORA-24247: network access denied by access control list (ACL) 错误.但是，执行此操作后，您可能会收到 ORA-29024: Certificate validation failure 错误.

This should get rid of the ORA-24247: network access denied by access control list (ACL) error. However, after doing this, you may get a ORA-29024: Certificate validation failure error instead.

如果发生这种情况，您需要创建一个 Oracle 钱包来存储 https://reqres 使用的根证书.在/(即Sectigo的那个)，确保数据库有权限读取这个钱包，并将钱包详细信息添加到对apex_web_service.make_web_request:

If this happens, you'll need to create an Oracle Wallet to store the root certificate used by https://reqres.in/ (i.e. the Sectigo one), ensure that the database has permission to read this wallet, and add the wallet details to the call to apex_web_service.make_web_request:

l_response := apex_web_service.make_rest_request(
  p_url => l_url,
  p_http_method => 'GET',
--p_parm_name  => apex_util.string_to_table('p_int_1:p_int_2'),
--p_parm_value => apex_util.string_to_table('12:12')
  p_wallet_path => 'path/to/your/wallet',
  p_wallet_pwd => 'YourWalletPasswordHere'
);

这篇关于设置 Oracle 12c R1 以连接到 Web 服务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！