Encountering a certificate validation failure while utilizing the APEX_WEB_SERVICE API to call a public web service prompted this error in a newly installed local Oracle APEX environment. This issue doesn’t arise when utilizing Oracle OCI Autonomous or APEX services, as Oracle automatically generates a TLS wallet, inclusive of almost every SSL/TLS certificate.
<![CDATA[
select apex_web_service.make_rest_request&#13;
(p_url =&gt; 'https://api.publicapis.org/entries',&#13;
p_http_method =&gt; 'GET') from dual;&#13;
/* Output */ &#13;
ORA-06512: at "APEX_230100.WWV_FLOW_WEB_SERVICES", line 1325&#13;
ORA-06512: at "APEX_230100.WWV_FLOW_WEB_SERVICES", line 897&#13;
ORA-29024: Certificate validation failure&#13;
ORA-06512: at "SYS.UTL_HTTP", line 380&#13;
<p>Failing to configure Access Control Lists (ACLs) when making web service calls triggers an “ORA-12545: Connect failed” error. ACLs serve as a vital security layer, enabling outbound requests from the APEX application to the targeted web services. In the provided example, an ACL is established to grant the TALA schema connection access to any hosts.</p> </div>
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(&#13;
host =&gt; '*',&#13;
ace =&gt; xs$ace_type(privilege_list =&gt; xs$name_list('connect'),&#13;
principal_name =&gt; 'TALA',&#13;
principal_type =&gt; xs_acl.ptype_db));&#13;
<p>Most of the https endpoints are secured by TLS/SSL certificates. This are the standard by all major web browsers to ensure a safer internet experience for users. Websites secured by TLS/SSL certificates are more trusted by internet users because they encrypt and protect private information transferred to and from their website. They also represent, or certify, your website’s brand identity.</p> </div>
<xmp># find the "xdb_wallet" folder and create a TLS_folder
# start from /opt directory
find . -type d -name "xdb_wallet"
./oracle/admin/FREE/xdb_wallet
# in this case it is located in ./oracle/admin/FREE/xdb_wallet
cd /opt/oracle/admin/FREE/xdb_wallet
# copy the exported certificate here
# log in to SQL command
sql /nolog
# command to create wallet
SQL&gt; orapki wallet create -wallet . -auto_login
# add certificate to wallet command
SQL&gt; orapki wallet add -wallet . -pwd <walletpassword> -trusted_cert -cert ISRG_Root_X1.crt;
# To view the certificate
SQL&gt; orapki wallet display -wallet ./;
<xmp>select apex_web_service.make_rest_request&#13;
(p_url =&gt; 'https://api.publicapis.org/entries',&#13;
p_http_method =&gt; 'GET',&#13;
p_wallet_path =&gt; 'file:/opt/oracle/admin/FREE/xdb_wallet/tls_wallet') from dual;
]]>
To simplify, the wallet path can be configured from the internal workspace, allowing
APEX_WEB_SERVICE
to use the configured
TLS_WALLET
by default, unless the
p_wallet_path
parameter is explicitly stated. Given that the wallet is set as auto-login, there is no need to provide the wallet password or the
p_wallet_pwd
parameter in the API call.
APEX_WEB_SERVICE
will use the TLS_WALLET configured here unless you specify the p_wallet_path parameter. Since I created the wallet as auto-login, I don’t need to put the wallet password and I don’t need to specify the p_wallet_pwd from the API.
