Today we'll talk about performing HTTPS requests with the APEX_WEB_SERVICE or UTL_HTTP packages in Application Express. At the first glance, this is pretty simple - instead of an URL beginning with http://, we use one beginning with https://.
As an example, we'll try out the HTTPS URL of the USGS (U.S. Geological Survey), which is also being used in the article about using REST services in Application Express. The following code example tries to fetch a JSON feed from earthquake.usgs.gov using HTTPS.
select apex_web_service.make_rest_request( p_url => 'https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/2.5_day.geojson', p_http_method => 'GET' ) from dual;
In most cases, the first attempt fails:
ORA-29273: HTTP request failed ORA-29024: Certificate validation failure ORA-06512: at "SYS.UTL_HTTP", line 380
Blogs and discussion forums are full of questions and answers how to deal with this situation. This article provides an explanation and explains how to solve the problem for PL/SQL as well as for Application Express:
The error message basically states that the Oracle database was not able to validate the SSL certificate, which the external web server sent in order to prove its identity. To get certificate validation working, we need to create an Oracle Wallet and configure it in Application Express.
But before doing that, here is a simple explanation about HTTPS and SSL certificates - "in a nutshell" (these explanations are far away from being complete, but they should be sufficient to understand what the Oracle Database is doing and what the Oracle Wallet is needed for):
When an HTTPS request to a web server is done, the first step is the SSL handshake, in which client and server negotiate about the details of the SSL encryption. As part of this handshake, the server sends its SSL certificate, which is similar to an ID card for humans: The certificate is there to prove, that the responding server is really the one, which the client expects.
The obvious question is now, whether the "ID card" is authentic - and this authenticity is certified by a Certificate Authority (CA). The web servers' SSL certificate is "signed" by the certificate authority using - again - a certificate. Now we can ask, whether the CA's certificate is authentic ... so it might be signed by another CA - and we have another certificate in the game. It's obvious that, at some point, this chain must come to an end. The client has to trust one CA certificate without looking into who has signed it - such a trusted certificate is then the end of the chain.
In a browser like Firefox, Chrome or others, all common CA certificates are pre-installed and this list of certificates is also being updated with each browser update. The Oracle Database maintains such certificates in an Oracle Wallet - but this wallet has to be created; and right after creation it is empty. We have to add the CA certificates we need.
This article explains how to create the wallet, how to load the required CA certificates into it and finally how to enable it for all Application Express workspaces and applications (read the full article).