The dreaded ORA-29024: Certificate validation failure... how to approach...

Certificates can be a pain.

So if you're trying to use UTL_HTTP or APEX_WEB_SERVICE to reach https destinations you'll be on the hook for obtaining certificates for the target server.

I had access to all the certificates separately and loaded them into a nice fresh new wallet. Unfortunately I couldn't get this to work. I'm working an SR with Oracle now but there doesn't seem to be much expertise on troubleshooting issues, even with

So in the meantime as with all SRs I press forward on my own. I tried different combinations of Certs that I had available, making sure I had enough to trace from Root, to any Intermediate, and to final host. I had acccess to the original certs I used for when I signed the host certs.

I finally found success by accessing the target API using Firefox and then using the Certificate screens in Firefox to download the complete cert chain as 1 file.

Obtaining the Cert

• Open Firefox. Navigate to the URL you want to access. Once the page loads, ensure the is a Padlock symbol next to the URL.
  
• Left click the Padlock. Select Connection Secure -> More Information -> View Certificate
  
• Scroll down the Miscellaneous -> Download -> PEM (chain)
  
• Click the link and download the cert chain. For our purposes it will be named website-com-chain.pem
  
• Load the Cert Chain into your wallet.
  

Preparing the Wallet

cd ~
mkdir mywebwallet
cd mywebwallet
orapki wallet create -wallet . -pwd [WalletPassword] -auto_login
orapki wallet add -wallet . -trusted_cert -cert ~/Downloads/website-com-chain.pem -pwd [WalletPassword]

Executing the Web Call

select utl_http.request('https://website.com/apicall/', 
                        null, 
                        'file:/home/oracle/mywebwallet', 
                        '[WalletPassword]') 
  from dual;