The request could not be mapped to any database

Today I figured out to my shame that the mentioned error by ORDS 3 has a really, REALLY simple cause... the password was expired.

Logging in (using SQL-plus) revealed this elegantly:

The second one of course did the trick.

Happy APEXING again.

Make tomcat wait until oracle database is available

Recently I've been installing the OATOA (Oracle APEX on Tomcat with ORDS behind Apache) stack a number of times on both Oracle Linux 7 and CentOS7.

In the past this was no problem, Tomcat came up and a request to http://myserver:8080/ords/ gave me the expected APEX login screen.

However since Oracle Linux 7 and CentOS7, it seemed that Tomcat started before it could find the Oracle database. A simple restart of the tomcat service would do the trick. On my virtual machine (recently switched from VirtualBox to parallels btw) meant for doing presentations no problem at all.

systemctl restart tomcat

Now I'm playing around with "droplets" at DigitalOcean, just another name for virtual servers in my opinion, but they have great features at a reasonable price. Take a look: https://m.do.co/c/187c0416a2b3.

But my droplets showed the same behaviour as my parallels virtual machine. I first had to restart tomcat to get a working system.

You all should know that I'm not a linux guru (really I'm not), so broke my brains about this for a looooong time.

Yesterday I finally managed to get this working. The idea is to make the tomcat start-script wait until it can see "something" on port 1521 (the db listener) and only then continue to start.

Open the file /usr/libexec/tomcat/server

nano /usr/libexec/tomcat/server

now add some lines of code _before_ the line that starts with MAIN_CLASS like this:

#!/bin/bash

. /usr/libexec/tomcat/preamble

# .=.=.=.=.= START Make tomcat wait on oracle .=.=.=.=.=
i=1
while netstat -lnt | awk '$4 ~ /:1521$/ {exit 1}'0
do
  sleep 10
  let i+=1
  if [ "$i" -gt "5" ]
  then
    break    #Abandon the loop.
  fi
done
# give Oracle some slack to also start the database
sleep 10
# .=.=.=.=.= END Make tomcat wait on oracle .=.=.=.=.=

MAIN_CLASS=org.apache.catalina.startup.Bootstrap

add the obvious lines to your script.

What is happening?
The line "netstat -lnt | awk ..." tries to find the string ":1521" in the result of a "netstat -lnt" command. This would indicate that something is listening on that port. In our case it will be the Oracle Listener.
If it does not find an open port 1521, it will sleep for 10 seconds, increment a counter ("i") by 1 and do the loop again. The loop will loop a maximum of five times to prevent an endless loop.
After the loop completes we give the Oracle database another 10 seconds to get started.

That should do the trick. It does for me (I use the same code at digitalocean as well as my parallels virtual linux server.

I'm sure linux must have some system in place that could do the trick as well (make one service dependable on the other) but I haven't found an easy one to use. If someone could help me out here, feel free to leave a comment.

Regards,
Richard

making https (webservice) requests from PL/SQL without a wallet

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:

1. your pl/sql program uses UTL_HTTP or APEX_WEB_SERVICE.MAKE_REST_REQUEST to do the API-request to your Apache proxy.
2. The proxy uses its configuration to forward the request to the final endpoint
3. The endpoint replies to your proxy
4. 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 https://www.linkedin.com/ or on http://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 http://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 stuborn and use:

http://revprox.local/www.linkedin.com/uas/oauth2/accessToken

as the API endpoint.

That's all folks.