Working with Dates Using ORDS

By October 3, 2016 Uncategorized

Oracle REST Data Services (ORDS) allows developers to create REST interfaces to Oracle Database, Oracle Database 12c JSON Document Store, and Oracle NoSQL Database as quickly and easily as possible. When working with Oracle Database, developers can use the AutoREST feature for tables or write custom modules using SQL and PL/SQL routines for more complex operations. This post will explain what happens with dates as they move between ORDS and Oracle Database.

Here’s an overview of what’s covered in this post:

Intro to datetime handling with ORDS

As data arrives from a REST request, ORDS may parse ISO 8601 strings and convert them to the TIMESTAMP data type in Oracle Database. This occurs with AutoREST (POST and PUT) as well as with bind variables in custom modules. Remember that TIMESTAMP doesn’t support time zone related components, so the datetime value is set to the time zone ORDS uses during the conversion process.

When constructing responses to REST requests, ORDS converts datetime values in Oracle Database to ISO 8601 strings in Zulu. This occurs with AutoREST (GET) and in custom modules that are mapped to SQL queries (GET). In the case of DATE and TIMESTAMP data types, which don’t have time zone related components, the time zone is assumed to be that in which ORDS is running and the conversion to Zulu is made from there.

Here are some general recommendations when working with ORDS for REST (i.e. not APEX):

  1. Ensure that ORDS uses the appropriate time zone as per the data in the database (i.e., the time zone you want dates going into the database). See the section on Setting the time zone that ORDS uses for details on how to do that.
  2. Don’t alter NLS settings (such as the time_zone) mid-stream.

Note that while I refer to ISO 8601 strings in this post, ORDS actually supports RFC3339 strings. RFC3339 strings are a conformant subset of ISO 8601 strings. The default format returned by JSON.stringify(date) is supported.

Working with dates using AutoREST

There are several ways to move data between ORDS and Oracle Database. AutoREST is the most declarative means, providing an easy and consistent way to create RESTful APIs that map to tables and views. There are PL/SQL APIs to script and automate the AutoREST enablement, but I want to demonstrate the use of the SQL Developer GUI for this example.

Creating the table and enabling AutoREST

The following assumes you have SQL Developer installed with a connection to the schema you want to use already configured.

  1. Start SQL Developer and click the plus sign next to the schema you’d like to use in the Connections pane. This will open a connection to that schema as well as a SQL Worksheet to execute commands against it.

dates-in-ords-autorest-example-1

  1. Copy and paste the following code into the SQL Worksheet. The table we’ll be creating has one column for each of the datetime data types in Oracle Database.
  1. In the SQL Worksheet, click the Run Script button. This will create the objects in the schema.

dates-in-ords-autorest-example-2

  1. In the Connections pane, right-click the schema you’re connected to and select REST Services > Enable REST Services. This will open the RESTful Services Wizard which is used to enable RESTful services for the schema (though individual tables and views will need to be enabled too).

dates-in-ords-autorest-example-3

  1. Check the Enable schema checkbox.
  2. Leave the default value of Schema alias (should match the schema name) for this example. The schema alias is used in the RESTful URIs so change it in the future if you don’t want to expose the actual schema name.
  3. Uncheck the Authorization required checkbox. Typically you’d want to secure your REST services but for the purposes of this demo we’ll disable authorization.
  4. Click the Next > button.

dates-in-ords-autorest-example-4

  1. Review the summary and then click the Finish button.

dates-in-ords-autorest-example-5

  1. Click the OK button to dismiss the success message.

dates-in-ords-autorest-example-6

  1. Open the Tables node, right-click the TODOS table, and select Enable REST Service. This will open the RESTful Services Wizard for an individual object.

dates-in-ords-autorest-example-7

  1. Check the Enable object checkbox.
  2. Leave the default value of Object alias for this example.
  3. Uncheck the Authorization required checkbox.
  4. Click the Next > button.

dates-in-ords-autorest-example-8

  1. Review the summary and then click the Finish button.

dates-in-ords-autorest-example-9

  1. Click the OK button to dismiss the success message.

dates-in-ords-autorest-example-10

That’s it, we now have full CRUD capabilities, plus some extra goodies such as filtering and batch load, on the table via REST!

Testing the AutoREST service

With the service now in place, I’ll test creating a todo (POST) and then retrieving it back out (GET). I’ll do this using the command line utility cURL. The following assumes that ORDS has been installed and is running. I’m running a local instance of ORDS in standalone mode through SQL Developer (Tools > REST Data Services > Run). You may need to make changes to the hostname, port, schema name, etc. to run the examples in your environment.

Let’s start by POSTing a todo into the table. The date we are working with is: 01-jan-2016 00:00:00.123456 America/New_York. When converted to JSON via JSON.stringify, it appears as “2016-01-01T05:00:00.123Z”. I’ll map that value to each of the four datetime columns in the todos table.

The response status code was 201 Created. Here’s is the response body I received.

ORDS returns the data that was inserted along with some links that can be useful to application developers (such as a link to the resource that was created). Notice that the dates came out as they went in except that the date column’s fractional seconds were lost.

Okay, let’s see what was actually inserted in the table. Using the SQL Worksheet in SQL Developer, execute the following query:

Here are the results I got (datetime formats can be set in Preferences > Database > NLS):

ID 1
Name Get milk
DUE_D 01-JAN-16 12.00 am
DUE_TS 01-JAN-16 12.00.00.123000000 AM
DUE_TSWTZ 01-JAN-16 12.00.00.123000000 AM AMERICA/NEW_YORK
DUE_TSWLTZ 01-JAN-16 12.00.00.123000000 AM

As you can see, the dates, despite having been sent across in Zulu, have been converted to the time zone that ORDS is using. In my case, that time zone is America/New_York, which was picked up from my operating system. If I were to change my time zone, restart ORDS, and then repeat the same POST, the dates would reflect the new time zone setting. That’s why it’s very important to ensure that ORDS is using the correct time zone.

Now let’s fetch the record back out with a GET. I could use the self URI that was returned from the POST, but I’ll use the more generic collection URI instead (there’s only one row anyway).

The response status code was 200 OK. Here’s the response body I received:

Once again, the dates have been converted back to Zulu. With the due_tswtz and due_tswltz columns, ORDS was provided with time zone related details in the data type that it could use to convert to Zulu. As for the due_d and due_ts, ORDS assumed the time zone of those dates was a match to the time zone it was using and converted to Zulu from there.

Working with dates in a custom module

In certain circumstances, you may decide that AutoREST isn’t the right fit and opt to create a custom module using SQL or PL/SQL where you have more control over parsing the input and generating the output. Such modules allow developers to define bind variables, which can be thought of as placeholders for values in the SQL and PL/SQL code. Just before execution of the code, values can be transferred in (bound in) from the REST/JSON request context to the code being executed. At the end of execution, values can be transferred back out (bound out) to the REST/JSON response context. It’s also possible to declare bind variables that work in both directions.

When values are bound in either direction, their data types are automatically converted from one context to another. Using the GUI in SQL Developer, I’ll create a new REST module that demonstrates binding an ISO 8601 string in JSON into a TIMESTAMP in SQL. The following assumes the REST Data Services panel in SQL Developer has been opened and a connection to ORDS has already been made.

Creating the custom module

  1. Right-click Modules.
  2. Click New Module.

dates-in-ords-bind-in-example-1

  1. Set Module Name to date_testing.
  2. Check the Publish checkbox.
  3. Click Next >.

dates-in-ords-bind-in-example-2

  1. Set URI Pattern to bind_in_example.
  2. Click Next >.

dates-in-ords-bind-in-example-3

  1. Leave all the defaults on this step and click Next >.

dates-in-ords-bind-in-example-4

  1. Review the summary and then click Finish.

dates-in-ords-bind-in-example-5

  1. Double-click the GET handler of the new module to open it.

dates-in-ords-bind-in-example-6

  1. Click the Parameters tab of the Get bind_in_test handler. The parameters section is where we can define the bind variables for the code defined in the SQL Worksheet tab. We’ll define a single in parameter to see how it works with the datetime data type that ORDS supports: TIMESTAMP.
  2. Set Name to json_date.
  3. Set Bind Parameter to bind_in_ts.
  4. Set Source Type to URI.
  5. Set Data Type to TIMESTAMP.

dates-in-ords-bind-in-example-7

  1. Click the SQL Worksheet tab.
  2. Copy and paste the code below into the SQL Worksheet. The query selects three things:
    • The value of the bind variable – allows us to see that the value in the SQL context will be converted to an ISO 8601 string going back to the REST/JSON response context.
    • The value of the bind variable converted to a string – allows us to see the value as it was in the SQL context. TO_CHAR is used to prevent any conversion of the datetime value going back to the REST/JSON response context.
    • A dump of the bind variable – allows us to verify the data type of the bind variable.
  1. Return to the REST Development panel and right-click the date_testing module.
  2. Click Upload.

dates-in-ords-bind-in-example-8

  1. Click the OK button to dismiss the success message.

dates-in-ords-bind-in-example-9

Testing the custom module

Now that the service is in place, we can test it with a browser or a command line tool like cURL. Here’s an example that uses cURL:

Notice that I’m passing an ISO 8601 formatted string to the query string parameter named json_date (defined in steps 11-15 above).

The following JSON is returned from the service. The items array (lines 2-8) contains the result of the query:

When the ts column was fetched it was converted to an ISO 8601 string in Zulu. The ts_chr column shows the actual value that was bound into the query (to_char prevents ORDS from doing any conversion). Notice the values of the two columns don’t match. Finally, the ts_dump column confirms that the data type bound in is a TIMESTAMP which matches what was selected in the wizard (180 corresponds to the TIMESTAMP datatype in the Oracle Built-in Data Types table). This behavior is consistent with what we saw in the AutoREST example.

Setting the time zone that ORDS uses

When ORDS is started, the JVM it runs in obtains and caches the time zone ORDS uses for various time zone conversions. By default, the time zone will be obtained from the operating system, so generally speaking the easiest way to change the time zone ORDS uses is to change the time zone of the OS and then restart ORDS or the application server it’s running on. Of course, the instructions for changing the time zone will vary as per the operating system.

If for any reason you don’t want to use the same time zone as the OS, it’s possible to override the default via the Java environment variable Duser.timezone. Exactly how that variable is set will depend on whether you’re running in standalone mode or in a Java application server. Here are some examples:

Standalone Mode

When running ORDS in standalone mode, it’s possible to set Java environment variables by specifying them as command line options before the -jar option.

Tomcat 8

In Tomcat 8, and possibly earlier and later versions too, it’s possible to set the time zone using the environment variable CATALINA_OPTS. The recommended way to do this is not to modify the CATALINA_BASE/bin/catalina.sh directly, but instead to set environment variables by creating a script named setenv.sh in CATALINA_BASE/bin.

I hope this post has answered any questions you may have surrounding how ORDS works with datetime values.

4 Comments

  • Steve Stowers says:

    Thank you, Dan!

    Great info detail.

    Steve

  • Ludovic Kuty says:

    Great answer. Since my PL/SQL code was not bulletproof for conversions like to_binary_float and the fact that migrating from EPG to ORDS made Oracle choose the NLS parameters for the session based on Java preferences, my code bugged. It should have used . as a decimal separator but it used , instead (FR locale).
    Your post helped me to fix the problem but since it was used as a service under Windows, I had to use C:\Program Files\Apache Software Foundation\Tomcat 8.5\bin\tomcat8w.exe to set Java options instead of setenv.bat (or right-click on systray then Configure…). Of course the settings were different and it was : -Duser.language=en AND -Duser.country=US.

Leave a Reply to danmcghan Cancel Reply