Relational to JSON With ORDS

Oracle REST Data Services (ORDS) was first released back in 2010 as the APEX Listener. REST support for JSON actually wasn't added until version 1.1 in 2011. Then in 2014, the product was renamed to Oracle REST Data Services to emphasize the commitment to REST.

At this point, ORDS does a lot! It still the best option for an APEX listener and APEX developers can still use the APEX GUI to create RESTful web services with ORDS. But there are new ways to create service definitions, including a GUI in SQL Developer and a PL/SQL API. We'll take a look at all three of those options in this post. We will not, however, be exploring the auto-REST enabling of schema objects or Simple Oracle Document Access (SODA) for REST capabilities. Those are exciting features I'll cover in future posts!

How does ORDS work? ORDS is a Java application that runs on a Java application server like WebLogic, GlassFish, or Tomcat. It basically acts as a middleman between clients (applications) and the database, mapping incoming HTTP(S) requests to resource handlers. Resource handlers handle one of the HTTP methods (GET, PUT, POST, etc.) for a specific URI pattern. Resource handlers can have different source types, such as query and PL/SQL. With the query source type, ORDS executes the query, converts the results into JSON, and returns the JSON to the client:

Solution 1: The Power of SQL

The following solution demonstrates the ability of ORDS to convert the results of a SQL query to a JSON object that represents a department in the HR schema. Because Oracle supports cursor expressions, you can nest SQL queries in such a way that they mimic the JSON object being created! Here's the SQL query we'll use to get the JSON:

Okay, not exactly the simplest query ever, but I think this is a great alternative to the PL/SQL code I wrote with the PL/JSON and APEX_JSON-based solutions. The fact that this is possible in SQL is amazing! Why not leverage it? We'll implement this solution using APEX.

REST Service Method 1: APEX

Assuming you have access to an APEX instance on the HR schema, creating the resource handler is pretty simple. Log into your APEX instance, navigate to the SQL Workshop > RESTful Services page and follow these steps:

1. Click the Create > button.
2. Set Name to HR.
3. Set URI template to departments/{id}.
4. Set Method to GET.
5. Set SourceType to Query One Row.
6. Enter the query from above in the Source field.

Once that's created you should be able to point your browser to the following URL to see the JSON for department 10 (adjust your host and port as needed): http://localhost:8080/ords/hr/departments/10.

Format: protocol://host:port/ords-name/apex-workspace-name/uri-template/bind-value

Output From SQL

When called with the department id set to 10 in the URL, the following JSON will be returned.

At first glance the JSON appears to be spot on. But upon closer inspection you might notice the following problems:

• The cursors were returned as arrays, not objects. In the case of employees, this is perfect, but for location and manager it wasn't exactly what we wanted.
• The case of my columns was not maintained. For example, streetAddress was returned as streetaddress.
• issenior had to be returned as a string or a number, as Boolean values are not valid in Oracle's SQL engine.
• Null values were omitted entirely. In our case, we wanted to show commissionPct with a null value if it was null in the database.

Both PL/JSON and APEX_JSON also have some form of SQL to JSON solutions, but they both suffer from similar drawbacks. For simpler JSON results and/or situations where you have some flexibility over the format of the JSON, the SQL to JSON solutions can be very convenient. However, when you need your JSON formatted precisely, PL/SQL is still an option with ORDS.

Solution 2: PL/SQL to the Rescue

Remember the PL/JSON and APEX_JSON-based solutions? We can leverage those in ORDS, too! In addition to executing SQL queries and converting the results to JSON, ORDS can simply return the contents of the HTP buffer. In fact, APEX_JSON default buffer is the HTP buffer so it's a great choice. I made some minor tweaks to that solution, turning the function that returned a CLOB into a procedure named put_dept_apex_json that simply writes to the HTP buffer.

We'll implement this solution using the remaining two methods to create web services with ORDS: SQL Developer and the PL/SQL API.

REST Service Method 2: SQL Developer

If you'd rather use a GUI in SQL Developer (v4.1+) instead of APEX, that's an option! First, you'll need to configure a user that can connect to ORDS via SQL Developer. I couldn't get the tomcat user repository to work for me but I was able to create a user to connect with using the ords.war (replace username and password with the credentials you'd like to use):

Next, you'll need to enable REST Services for the schema you'd like to develop on. To do that, connect SQL Developer to that schema, then right-click the connection and select REST Services > Enable REST Services:

Once that's done you can open SQL Developer and click View > REST Data Services > Development to open the REST Development panel. Click the connect button (looks like a socket over a globe) and then click the green + button to create a new connection. Notice I'm using HTTP over HTTPS for development — this must be configured:

Once that's done you can make a connection to ORDS, right-click on Modules, and then on New Module... to open the RESTful Services Wizard:

• Set Module Name to hr.
• Set URI Prefix to api. This is important because of a bug that causes problems when this field is null.
• Check the Publish option.
• Click Next >.

• Set URI Pattern to departments/:id.
• Click Next >.

• Set Source Type to PL/SQL.
• Click Next >.

• Click Finish.

• Return to the REST Development panel and expand the new hr module until you reach the GET resource handler. Click that to open it.

• Enter enough PL/SQL to invoke the stored procedure into the Worksheet of the GET resource handler.
• Close the resource handler.

• Return to the REST Development panel and right-click the hr module.
• Click Upload… to upload the module to the database.

Once that’s done you should be able to point your browser to the following URL to see the JSON for department 10 (adjust your host and port as needed):
http://localhost:8080/ords/hr/api/departments/10.

Format: protocol://host:port/ords-name/db-schema-name/uri-prefix/uri-pattern/bind-value

Workspace vs. Schema: So what happens when you have a schema enabled for REST and you have an APEX workspace with the same name? That’s like asking, “what would happen if two people occupied the same space at the same time?” Bad things! Turns out APEX workspaces take precedence over schemas. This can result in lots of “404 Not Found” errors due to ORDS looking for routes in APEX when you wanted it to look in a schema. You have been warned!

REST Service Method 3: PL/SQL API

ORDS now supplies a PL/SQL API that can be used to create REST services programatically. Currently, the documentation of the API covers ORDS and OAUTH. We can create the service we just created using the SQL Developer GUI with just this:

begin

   ords.create_service(
      p_module_name => 'hr' ,
      p_base_path   => 'api',
      p_pattern     => 'departments/:id',
      p_source_type => 'plsql/block',
      p_source      => 'begin put_dept_apex_json(:id); end;'
   );

   commit;

end;

Not bad… With APIs like this you can script deployments or even create your own GUIs!

Output From PL/SQL

As would be expected, when called with the department id set to 10 in the URL, the JSON returned from the PL/SQL based solution matches the goal 100%.

{
  "id": 10,
  "name": "Administration",
  "location": {
    "id": 1700,
    "streetAddress": "2004 Charade Rd",
    "postalCode": "98199",
    "country": {
      "id": "US",
      "name": "United States of America",
      "regionId": 2
    }
  },
  "manager": {
    "id": 200,
    "name": "Jennifer Whalen",
    "salary": 4400,
    "job": {
      "id": "AD_ASST",
      "title": "Administration Assistant",
      "minSalary": 3000,
      "maxSalary": 6000
    }
  },
  "employees": [
    {
      "id": 200,
      "name": "Jennifer Whalen",
      "isSenior": true,
      "commissionPct": null,
      "jobHistory": [
        {
          "id": "AD_ASST",
          "departmentId": 90,
          "startDate": "17-SEP-1995",
          "endDate": "17-JUN-2001"
        },
        {
          "id": "AC_ACCOUNT",
          "departmentId": 90,
          "startDate": "01-JUL-2002",
          "endDate": "31-DEC-2006"
        }
      ]
    }
  ]
}

Summary

ORDS is a pretty amazing tool when it comes to creating REST services. As I mentioned before, this post only covered a small portion of all of the features ORDS has to offer.

While I’m a huge fan of SQL to JSON conversion, I’ve not yet seen an implementation that allows for the kind of control needed to meet the requirements of the goal in this series. Luckily, ORDS allows one to use PL/SQL to generate JSON manually in such situations. The combination of ORDS and APEX_JSON is really powerful!