When using Oracle’s UTL_HTTP or HTTPURITYPE to access external HTTPS services like Google Translate, you may encounter certificate validation errors due to missing trusted certificates in the Oracle wallet.

Problem: Certificate Validation Failure

Running the following query results in an error:

SELECT httpuritype.createuri('https://translate.google.com').getclob() FROM dual;

Error:

ORA-29273: HTTP request failed  
ORA-06512: at "SYS.HTTPURITYPE", line 38  
ORA-06512: at "SYS.UTL_HTTP", line 1288  
ORA-29024: Certificate validation failure  
ORA-06512: at "SYS.UTL_HTTP", line 651  
ORA-06512: at "SYS.UTL_HTTP", line 1278  
ORA-06512: at "SYS.HTTPURITYPE", line 25

This is caused by the absence of a trusted SSL certificate from translate.google.com in the Oracle Wallet used by UTL_HTTP.

Recommended Approach

DO NOT use your TDE wallet (used for database encryption) to store trusted certificates for UTL_HTTP.

Oracle recommends creating a separate wallet for this use case.

🛠️ Steps to Configure Wallet for Google Translate

1. Create a new wallet directory

mkdir -p /opt/oracle/dcs/commonstore/wallets/http_wallet

2. Create the wallet

oraapki wallet create -wallet /opt/oracle/dcs/commonstore/wallets/http_wallet \
-pwd password -auto_login

Expected Output:

	Oracle PKI Tool Release 19.0.0.0.0 - Production
	Version 19.4.0.0.0
	Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.

	Operation is successfully completed.

3. 📥 Import Google’s root certificate

Ensure you have downloaded the correct certificate from a browser or using tools like openssl, and save it as google-com.pem.

sample screenshot from firefox ( from view certificate downlaod)

Now, add it to the wallet:

orapki wallet add -wallet /opt/oracle/dcs/commonstore/wallets/http_wallet \
-trusted_cert -cert /home/oracle/google-com.pem -pwd password

4. 🔍 Validate the wallet contents

orapki wallet display -wallet /opt/oracle/dcs/commonstore/wallets/http_wallet

Expected Output:

Trusted Certificates:
Subject:        CN=GTS Root R1,O=Google Trust Services LLC,C=US

🧪 Test Google Translate Access

5. Set the wallet in your session

BEGIN
  UTL_HTTP.set_wallet('file:/opt/oracle/dcs/commonstore/wallets/http_wallet', 'password');
END;
/

6. Run the HTTP request

SELECT UTL_HTTP.REQUEST('https://translate.google.com') FROM dual;

You should now get a valid response (HTML output), confirming successful SSL handshake and certificate validation.

📌 Summary

• Use a separate wallet for SSL requests via UTL_HTTP
• Import the root certificate of the target HTTPS service
• Use orapki to manage wallets and trust stores
• Set wallet path and password using UTL_HTTP.set_wallet