ORDS and DATES in your TEMPLATEs

You’ll at some point need to use a DATE as a predicate to get an item or collection of items out of your Oracle Database REST APIs.

Here’s our least favorite, yet most common example, HR.EMPLOYEES.

Let’s build a RESTful Service that lets me pull ALL of the employees hired on a certain DAY.

Step One: Define the Template

Step Zero would be to define the module, but the real work comes into play for how you want to define your REST Service item or collection.

I’ve gone with a MODULE called ‘predicates’ that has a base path of ‘predicates/’ and I decided to have my Template be defined as

dates/:stringdate

I want to retrieve data from my table, so I’m going to do this with SQL, and I’m going to use the GET handler.

Step Two: Add a GET Handler

In my TEMPLATE I have the text, ‘:stringdate’. When building the ORDS RESTFul Service Handler SQL code block, I can use that exact same string to refer to the value that’s included in the HTTPS Request Uniform Resource Identifier (URI).

As an example, we could do –

SELECT :stringdate FROM dual

And if we saved/published this, and called it with a ‘DATE’ of say, ’21-SEP-89′ –

You can see that we are getting our string in from the request URI and passing it to our GET handler, all just by using the bind variable :stringdate in BOTH the template definition AND the handler SQL block.

Now, we wanted ALL of the employees who started on the same ‘DAY’ – so we don’t care WHAT TIME they were hired, AND we assume there can be more than ONE, since HIRE_DATE isn’t a KEY or UNIQUE. The other nice thing is we don’t have to worry about NULLs because the column is defined as NOT NULL.

ORDS is going to read in the value off the request URI as a STRING, but we’re going to be using SQL to do a compare based on a DATE field.

So, we either have to convert HIRE_DATE to a string, OR we have to convert the bind variable to a DATE…or we could live EXTREMELY DANGEROUSLY and let the database implicitly convert the string to a date.

So, what will our SQL look like?

SELECT *
  FROM employees
 WHERE to_char(hire_date, 'DD-MON-YYYY') = :stringdate

You can see that I went with converting the HIRE_DATE column in the database to a string via the TO_CHAR() function.

If this is an INDEXED field, using a function against it, could make the index useless, impacting performance. TEST, TEST, TEST!

I’m writing the SQL, I’m designing the API, so I get to pick the format of the data coming in and out.

My code block is expecting a date with a string format of ‘DD-MON-YYYY.’

So, if I do a GET on predicates/dates/21-SEP-1989 –

But Jeff, I need to use a different date format.

Ok, let’s assume for REASONS that you need to be able to use a ‘/’ when separating the month, day, and year in your URI for the employees.

This is annoying, but not hard.

Two things must change.

Our handler code needs to change:

WHERE to_char(hire_date, 'DD/MON/YYYY') = :stringdate

And when calling the service, we need to URL Encode the string. A ‘/’ is reserved when working with URIs. If I pull up this URI:

/predicates/dates/21/SEP/1989

For it to work, as is, the TEMPLATE would need to be ‘/predicates/dates/:day/:month/:year’ and our HANDLER SQL would look like

So to avoid that mess, we can keep our TEMPLATE, and just pass the string with the /’s in them, escaped.

Like so –

Don’t trust implicit conversions.

That thing above I said ‘NOT TO DO’ – you are going to be tempted to do it. I know this, because I’ve seen it done, MANY times. People email me for help, and one of the first things we look for to explain ‘weird problems with results’ is DATEs and TIMEZONEs.

You CANNOT take it for granted that your database or session NLS_DATE_FORMAT is going to be a particular pattern. As soon as you do, you’re going to be wrong at a certain point, and your APIs won’t work reliably. So BUILD the date format into your query.

Hey Jeff, what’s with the Zulu stuff?

When our EMPLOYEE comes back, their ‘hire_date’ attribute in the JSON response is formatted as date time with timezone, the timezone being UTC. This is how JDBC treats dates, so even when your Oracle DATE doesn’t have a TZ, it gets added. This is a deep/yuck topic, deserving it’s own thread, but for now, let’s say you don’t want that to happen.

Easy answer, do this –