Problem

Trying to connect to a web site on the Internet, you get error:

SQL> select utl_http.request('https://google.com') from dual;

ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1530
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1470
ORA-06512: at line 1

Solution

There could be two issues causing the above error:

1) The web host is not supporting TLSv1.2, or

2) Oracle wallet is not used, or the wallet is lacking the required Root CAs and/or Intermediate certificates to validate the web resource

To add the required certificates to an Oracle wallet:

1) Create an Oracle wallet if one does not yet exist. See link for more help

Oracle Database 19c -- Create an Oracle Wallet to Store SSL Certificates

2) Identify all the intermediate and the Root CA certificates of the web resource:

$ openssl s_client -connect google.com:443 -showcerts < /dev/null
CONNECTED(00000003)
depth=3 C = BE, O = GlobalSign nv-sa, OU = Root CA, CN = GlobalSign Root CA
verify return:1
depth=2 C = US, O = Google Trust Services LLC, CN = GTS Root R1
verify return:1
depth=1 C = US, O = Google Trust Services LLC, CN = GTS CA 1C3
verify return:1
depth=0 CN = *.google.com
verify return:1
---
Certificate chain
0 s:/CN=*.google.com
i:/C=US/O=Google Trust Services LLC/CN=GTS CA 1C3
-----BEGIN CERTIFICATE-----
>>> skipped lines <<<
-----END CERTIFICATE-----
1 s:/C=US/O=Google Trust Services LLC/CN=GTS CA 1C3
i:/C=US/O=Google Trust Services LLC/CN=GTS Root R1
-----BEGIN CERTIFICATE-----
>>> skipped lines <<<
-----END CERTIFICATE-----
2 s:/C=US/O=Google Trust Services LLC/CN=GTS Root R1
i:/C=BE/O=GlobalSign nv-sa/OU=Root CA/CN=GlobalSign Root CA
-----BEGIN CERTIFICATE-----
>>> skipped lines <<<
-----END CERTIFICATE-----
---
Server certificate
subject=/CN=*.google.com
issuer=/C=US/O=Google Trust Services LLC/CN=GTS CA 1C3
---
No client certificate CA names sent
>>> skipped lines <<<
Protocol  : TLSv1.2
>>> skipped lines <<<
Start Time: 1684782989
Timeout   : 300 (sec)
Verify return code: 0 (ok)
---
DONE

The certificate depth=0 (the certificate of the web resource itself) is not required. All the other certificates in the chain, starting with depth=1 to the Root CA certificate are to be saved in a txt file. Copy paste each certificate's content including the "-----BEGIN CERTIFICATE-----" and "-----END CERTIFICATE-----" into a separate file

$ cat dep1.crt
-----BEGIN CERTIFICATE-----
>>> base64 encoded certificate content <<<
-----END CERTIFICATE-----

3) Add the certificates to the wallet:

$ orapki wallet add -wallet <wallet_location> -trusted_cert -cert <path_to cert_file>

4) Access the Internet resource, specifiying the wallet location:

SQL select utl_http.request(
'https://google.com', null,
'<wallet_location>')
from dual;

For PL\SQL code, you may also use UTL_HTTP.SET_WALLET procedure for convinience