Relational to JSON with ORDS

By July 13, 2015 Uncategorized

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 the newly added Simple Oracle Document Access (SODA) for REST capabilities. Those are exciting new features I’ll cover in future posts! đŸ™‚

How does ORDS work? ORDS is a Java application that runs in 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:

ords-overview

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 Source Type to Query One Row.
  6. Enter the query from above in the Source field.

rest-apex-create-module

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

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’s 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 now 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”:

rest-ords-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 plus button to create a new connection. Notice I’m using HTTP over HTTPS for development – this must be configured:

rest-ords-make-connection

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:

rest-ords-new-module

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

rest-ords-wizard-1-4

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

rest-ords-wizard-2-4

  1. Set Source Type to PL/SQL.
  2. Click Next >.

rest-ords-wizard-3-4

  1. Click Finish.

rest-ords-wizard-4-4

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

rest-ords-open-get-resource-handler

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

rest-ords-add-plsql-to-resource-handler

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

rest-ords-upload-resource-handler

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

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:

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

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!

12 Comments

  • Etm says:

    Have you attempted to use filters on a query containing cursor expressions? I’m getting 400 Bad Request when I try to combine them, but removing either the filter or the cursor works just fine.

  • Mukesh Negi says:

    Hi,

    Great article.

    I need your help to implement one of my requirement. Any help would be greatly appreciated.

    I have two different data sources, like Oracle db1 and Oracle db2. I would like to extract data from db1, then need to convert or you can say arrange in different table structure as of db2, and then with the help of API need to migrate that data into db2 in using JSON format.

  • Marco says:

    Hi Dan can we consume data from Server A and post it to Server B.

    I have situation where by i need to collect data from Server A’s table and then send it to B. Server B has Post method Web service created.
    I can not use dblink due to some reasons. Can you guide me in right path.
    How can i achieve it..
    I have tried sending normal TEXT from Server A to B but it was not successful.

    declare
    t_http_req utl_http.req;
    t_http_resp utl_http.resp;
    t_request_body varchar2(30000);
    t_respond varchar2(30000);
    t_start_pos integer := 1;
    t_output varchar2(2000);

    begin

    t_request_body := ‘the data you want to send to the webservice’;
    t_http_req:= utl_http.begin_request( ‘http://myhost/apex/schema/modulename’
    , ‘POST’
    , ‘HTTP/1.1’);
    utl_http.set_header(t_http_req, ‘Content-Type’, ‘text/xml charset=UTF-8’);
    utl_http.set_header(t_http_req, ‘Content-Length’, length(t_request_body));
    utl_http.write_text(t_http_req, t_request_body);
    t_http_resp:= utl_http.get_response(t_http_req);
    utl_http.read_text(t_http_resp, t_respond);
    utl_http.end_response(t_http_resp);
    end;

  • Doug says:

    Hello,

    Great write up.

    Any suggestions on how to save the JSON to a file?

  • David Gaskell says:

    Hi Dan,

    I new to ORDS and just starting to review the cabilities. Above you discuss your ammended stored procedure “put_dept_apex_json” that simply writes to the HTP buffer. Are you able to share this example please?

    Thanks in advance
    David

    • danmcghan says:

      Hi David,

      The original “get_dept_apex_json” can be found here:
      https://jsao.io/2015/07/relational-to-json-with-apex_json/

      All I did was change the function to a procedure. Then I removed line 27 and lines 141 to 145. Let me know if that’s sufficient…

      Dan

      • David Gaskell says:

        Thanks for the Reply Dan. I think the area I was unsure on was when you mention writing to the HTP buffer. Is this done without changing any other code? I’m unsure how you can then verify the JSON object has been created correctly. I have tried running this however I’m getting a HTTP 404 error suggesting the URL suggesting it was not found. However the URL seems correct so I’m concerned I maybe missing something else

      • David Gaskell says:

        Dan, Thanks again for your help. I have this working now so you can ignore my last comment.

Leave a Reply