While developing the application for my kscope15 presentation I discovered that my Oracle XE database is unable to handle the certificate for Linked-in. This has to do with the algorithm used to sign the Linked-in certificate. Oracle XE 11 has a bug that prevents it from handling the certificate correctly.
The solution lies in creating a proxy in between your database and the final API endpoint.
Like this:
your pl/sql program uses UTL_HTTP or APEX_WEB_SERVICE.MAKE_REST_REQUEST to do the API-request to your Apache proxy.
The proxy uses its configuration to forward the request to the final endpoint
The endpoint replies to your proxy
Your proxy reverse proxies it to the requesting code inside your database
In this drawing the green numbers are regular HTTP requests and the orange numbers represent HTTPS requests and responses.
This all sounds very promising. Getting rid of the Oracle wallet seems like a good idea. However you can set the greens to be HTTPS as well, in which case you only need to have one certificate in your wallet.
Considerations
My Oracle database server uses a private IP range.
My Apache server is accessible from the internet, but has a second network interface connected to the private IP range.
Setting up your proxy
Setting up the proxy consists of creating a new (virtual) site on your server. I chose to use the domain "revprox.local" because this domain will never get resolved into a real IP-number:
I now need to edit the httpd.conf for my apache server:
RewriteEngine On
ProxyVia On
## proxy for linkedin
ProxyRequests Off
SSLProxyEngine On
Order deny,allow
Allow from all
ProxyPass /www.linkedin.com/ https://www.linkedin.com/
ProxyPassReverse /www.linkedin.com/ https://www.linkedin.com/
ProxyPass /api.linkedin.com/ https://api.linkedin.com/
ProxyPassReverse /api.linkedin.com/ https://api.linkedin.com/
As you see in the linked-in API documentation, all API endpoint either are on linkedin.com or on api.linkedin.com. It is therefore sufficient to define these two in my httpd.conf.
Setting up your database
As I mentioned before, the url revprox.local will never resolve into anything useful. We must tell the database what to do when a request for revprox.local comes around.
We now only need to alter the /etc/hosts file as thus:
192.168.1.1 revprox.local
The trick lies in the last line:
192.168.1.1 is the private IP number of my apache-proxy
In the example I removed extra lines that are not relevant for my story
Making a web-request
Now we set up the proxy and made changes to the /etc/hosts file we can actually start using them.
For example, when getting an oauth2 token from linked-in, the documentation tells us to make a request to:
https://www.linkedin.com/uas/oauth2/accessToken
Instead we will be stubborn and use:
http://revprox.local/www.linkedin.com/uas/oauth2/accessToken
as the API endpoint.
That's all folks.