Preventing 500 Status Codes with Oracle REST Data Services

In my Oracle REST Services Demos I always show things working exactly as planned.

But what happens when your user does something your program doesn’t expect?

Or what happens when you code does something you don’t expect? In the web (HTTP) world, you get a 500.

10.5.1 500 Internal Server Error
The server encountered an unexpected condition which prevented it from fulfilling the request.

Thankfully, PL/SQL provides EXCEPTIONS. We can say, when this bad thing happens, run this code instead. And really, as developers we should be good enough to expect certain problems to occur and to plan for them.

When something doesn’t work in your database code attached to a RESTful Service, you get a HTTP Status code of 500.

This isn’t friendly.

Let’s look at an un-handled exception in ORDS from the user perspective.

Bad

Boo!

This looks quite unprofessional. Not the warm and fuzzy feel you want when doing business with a partner or vendor. What if that was the response I got when trying to buy the WiFi on my airline with my credit card? (A completely fake/did not really happen in real life scenario which happened to me yesterday).

Less Bad

This is much better, or less bad than a 500.

We have a proper status code and a message returned from the server (ORDS).

Now, a perfect scenario would include having some client-side validation of the inputs, preventing me from sending bad values in the first place. But even then, it’s best to plan for the common scenarios. For example, what if your service is available OUTSIDE the intended application where there is no validation of inputs happening?

Anyways, let’s see how I made this happen. And it’s quite simple really.

The Exception

When you try to select a string into a number in Oracle, you get a ORA-06502. And if we look into the ORDS log, we can see this pop out when I make the bad call w/o handling the exception:

I’ve set the out going message/response to something a bit more helpful than a ¯\_(ツ)_/¯ and I’ve set the response to a HTTP 400, which means:

‘10.4.1 400 Bad Request – The request could not be understood by the server due to malformed syntax. The client SHOULD NOT repeat the request without modifications.‘

Note to pass the status back I had to set a parameter for my handler code for ‘X-ORDS-STATUS-CODE’

This is new for 18.3 of ORDS, the X-APEX codes are being deprecated.

I’m Getting 500’s but I Don’t Know Why?

You need to get to the ORDS standard output logs. If you’re running ORDS, hopefully you’re logging that somewhere. If it’s Tomcat (Catalina) or WLS, they have places for that. If it’s in standalone mode, you need to redirect that out to a file yourself.

By the way, DO NOT DO THIS IN PROD. You will be exposing details of your database to people you do not want to. Like those mysql errors you see when you try to hit a website and it overloads their system. Oh, that’s MySQL, and they have a table named ‘XYZ’…ahhh. Yeah, that’s bad.

The Code

My ORDS handler pulls the value out of the header (num_in) and tries to convert it to a number (x) – which works just fine if your string just happens to be a number already. I then pass back a message saying, hey, thanks for passing me that number, and here it is again just so you know I got it correctly.