• Intro to datetime handling with ORDS
• Working with dates via AutoREST
• Creating the table and enabling AutoREST
• Testing the AutoREST service
• Working with dates in a custom module
• Creating the custom module
• Testing the custom module
• Setting the time zone that ORDS uses

Intro to datetime handling with ORDS

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.

Working with dates using AutoREST

Creating the table and enabling AutoREST

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.

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.

create sequence todos_seq;

create table todos(
  id         number, 
  name       varchar2(100 byte), 
  due_d      date, 
  due_ts     timestamp (6), 
  due_tswtz  timestamp (6) with time zone, 
  due_tswltz timestamp (6) with local time zone
);

create or replace trigger todos_biu_trg
  before insert or update on todos
  for each row
begin

  if :new.id is null
  then
    :new.id := todos_seq.nextval();
  end if;

end;
/

1. In the SQL Worksheet, click the Run Script button. This will create the objects in the schema.

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).

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.

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

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

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.

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.

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

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

Testing the AutoREST service

$ curl -X "POST" "http://localhost:8080/ords/date_test/todos/" \
>      -H "Content-Type: application/json; charset=utf-8" \
>      -d "{\"name\":\"Get milk\",
>        \"due_d\":\"2016-01-01T05:00:00.123Z\",
>        \"due_ts\":\"2016-01-01T05:00:00.123Z\",
>        \"due_tswtz\":\"2016-01-01T05:00:00.123Z\",
>        \"due_tswltz\":\"2016-01-01T05:00:00.123Z\"}"

{
  "id": 1,
  "name": "Get milk",
  "due_d": "2016-01-01T05:00:00Z",
  "due_ts": "2016-01-01T05:00:00.123Z",
  "due_tswtz": "2016-01-01T05:00:00.123Z",
  "due_tswltz": "2016-01-01T05:00:00.123Z",
  "links": [
    {
      "rel": "self",
      "href": "http://localhost:8080/ords/date_test/todos/AAAhiNAAJAABCoLAAE"
    },
    {
      "rel": "edit",
      "href": "http://localhost:8080/ords/date_test/todos/AAAhiNAAJAABCoLAAE"
    },
    {
      "rel": "describedby",
      "href": "http://localhost:8080/ords/date_test/metadata-catalog/todos/item"
    },
    {
      "rel": "collection",
      "href": "http://localhost:8080/ords/date_test/todos/"
    }
  ]
}

select *
from todos

$ curl -X "GET" "http://localhost:8080/ords/date_test/todos/"

{
  "items": [
    {
      "id": 1,
      "name": "Get milk",
      "due_d": "2016-01-01T05:00:00Z",
      "due_ts": "2016-01-01T05:00:00.123Z",
      "due_tswtz": "2016-01-01T05:00:00.123Z",
      "due_tswltz": "2016-01-01T05:00:00.123Z",
      "links": [
        {
          "rel": "self",
          "href": "http://localhost:8080/ords/date_test/todos/AAAhiNAAJAABCoLAAE"
        }
      ]
    }
  ],
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "count": 1,
  "links": [
    {
      "rel": "self",
      "href": "http://localhost:8080/ords/date_test/todos/"
    },
    {
      "rel": "edit",
      "href": "http://localhost:8080/ords/date_test/todos/"
    },
    {
      "rel": "describedby",
      "href": "http://localhost:8080/ords/date_test/metadata-catalog/todos/"
    },
    {
      "rel": "first",
      "href": "http://localhost:8080/ords/date_test/todos/"
    }
  ]
}

Working with dates in a custom module

Creating the custom module

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

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

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

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

1. Review the summary and then click Finish.

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

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.

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.

select :bind_in_ts as ts,
  to_char(:bind_in_ts, 'dd-mon-yyyy hh24:mi:ss.ff') as ts_chr,
  dump(:bind_in_ts) as ts_dump
from dual;

1. Return to the REST Development panel and right-click the date_testing module.
2. Click Upload.

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

Testing the custom module

$ curl -X "GET" "http://localhost:8080/ords/date_test/bind_in_example?json_date=2016-01-01T05:00:00.123Z" -H "Content-Type: application/json"

{
  "items": [
    {
      "ts": "2016-01-01T05:00:00.123Z",
      "ts_chr": "01-jan-2016 00:00:00.123000000",
      "ts_dump": "Typ=180 Len=11: 120,116,1,1,1,1,1,7,84,212,192"
    }
  ],
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "count": 1,
  "links": [
    {
      "rel": "self",
      "href": "http://localhost:8080/ords/date_test/bind_in_example?json_date=2016-01-01T05:00:00.123Z"
    },
    {
      "rel": "describedby",
      "href": "http://localhost:8080/ords/date_test/metadata-catalog/item"
    },
    {
      "rel": "first",
      "href": "http://localhost:8080/ords/date_test/bind_in_example?json_date=2016-01-01T05:00:00.123Z"
    }
  ]
}

Setting the time zone that ORDS uses

$ java -Duser.timezone=America/New_York -jar ords.war standalone

CATALINA_TIMEZONE="-Duser.timezone=America/New_York"
CATALINA_OPTS="$CATALINA_OPTS $CATALINA_TIMEZONE