ORDS allows you to capture or expose data in an Oracle Database (relational or NoSQL) using the ubiquitous REST protocol. It is delivered as a Java application (war file) that can run standalone or within a J2EE container (think Apache Tomcat, GlassFish, WebLogic). Used in conjunction with the APEX5 web service and JSON parsing PL/SQL APIs, it can turn your Oracle database into a fully functional integration hub. I won't regurgitate the product data sheet, but other goodies that come with ORDS include OAUTH2 authentication, automatic parsing of SQL into JSON, automatic handling for HATEOAS (Hypermedia as the Engine of Application State), automatic paging for GET services and read consistency to name but a few.

Many customers have asked me "when should I use ORDS"? As is typical for someone who was a consultant for 18 years, my answer is usually "it depends" and in this case it really does. The best way I can describe it is to walk through some use cases and illustrate why ORDS is appropriate.

Exposing data RESTfully
The primary use case is to expose existing data as REST services. One area where I have seen this used extensively is exposing Oracle e-Business suite data as REST services. There are a vast array of use cases just here, think mobile applications for EBS users, facilitating connectivity to other middleware, facilitating MDM by making Customer or Item Master data easier to consume by other systems (and the list goes on).

Data Capture Machine
ORDS is a fast and light weight method of allowing you to capture data. Think IOT (Internet of Things). There is a vast amount of data coming at us and we need a way to easily capture it and store it using fast industry standard protocols. With ORDS, an Oracle NoSQL database and an AWS EC2 instance, you have a data capturing machine for very little cost.

Integration MiddleWare
On it's own ORDS is not capable of this, but given it runs on an Oracle database, why not take advantage of this and roll your own low cost integration middleware. After all, what is middleware, other than the ability to capture data (ORDS), transform it (PL/SQL and Oracle DB tables) and then pass it on to other systems (APEX_WEB_SERVICE). Most middleware tools use a database as a dehydration store anyway so why not make the database the heart of it all?

Facilitating Point to Point Integrations
Yes, I know we are in the 21st century and point to point integrations are frowned upon. Unless you have thousands (and thousands) of dollars to spend on complete middleware solutions (and the developers that come with them) you may not be able to afford that luxury. I can't tell you how many small to medium sized business I have come across that have an Oracle database and just need to be able to consolidate data from Salesforce and e-Business Suite so they can put together some consolidated reporting. In many cases the Oracle database, APEX and ORDS is the right sized solution for these companies. Even if these organizations grow in the future, they will already have REST services which are much easier to plug into middleware solutions such as Dell Boomi, Fusion Middleware and Mule.

Before diving into ORDS and exposing data left and right (which is both tempting and very easy to do), I strongly suggest you get a handle on REST principals first. It is very easy to abuse REST and create services like getCustomer or updateItem and pay no heed to versioning. Coming from a database programming background, I had to check myself constantly at the beginning.
​
The good news is that REST principals are not rocket science. Once you understand that you should be basing your services on resources (item, customer, sales order etc.) and allow the HTTP protocol to provide the action/verb (GET, POST, PUT, DELETE & PATCH) you are half way there.

Instead of me going into detail on my opinions on REST, I can save myself a lot of time and make a recommendation. When I started with REST I read a paper called 'Web API Design - Crafting Interfaces that Developers Love' by Brian Mulloy at APIGEE. Not only did it make sense to me but it also did not come with the 'though shalt do this' and 'though shalt do that doctrine' that you sometimes get. I try to follow these principals and they have worked well for me so far. You can get a copy of the paper from APIGEE's web site Direct Link. As an aside, APIGEE must know something about web services as Google just bought them Google to Aquire APIGEE.

Whilst by no means comprehensive, I want to leave you with some thoughts on best practices for implementing great APIs with ORDS.

Think About the URL
Given an ORDS URL is composed as follows​:
     https://example.com/<warfile>/<schemaname>/<module>/<service>

• Remove 'ords' from your URL. You can do this by either renaming ORDS.war to something else e.g. API.war or using a routing rule in your web server.
• When you are enabling your schema for ORDS, think about how it will reflect in the web service URL. Just because you have a schema you want to ORDS enable called 'jonsscoolchema' does not mean you want the world to see that:
  • BEGIN
    ORDS.ENABLE_SCHEMA(
       p_schema                        => 'JONSCOOLSCHEMA',
       p_url_mapping_type      => 'BASE_PATH',
       p_url_mapping_pattern => 'erp',
       p_auto_rest_auth            => FALSE);  
    END; 
• If you want to version your APIs (which I recommend you do) then use the ORDS module as the version e.g. name your modules v1, v2 etc. That way when you introduce a new version, you can keep the old one around for a while until folks have moved to the shiny new one.
• Generally:
  • http://example.com:8080/api/erp/v1/customer/1234
    • looks a lot better (and more professional) than:
  • http://example.com:8080/ords/erp/customer/getCustomer?customerId=100

Deployment
The ORDS team have given us a set of ORDS and OUATH PL/SQL APIs for defining, changing and deleting modules, handlers, parameters, clients etc. You can now construct SQL scripts to deploy your services making the process both quicker, more precise and less error prone. Use it!

Put Logic in PL/SQL Packages
Limit the logic in your ORDS handler to what you absolutely need to capture ORDS bind variables. Move as much of the web service logic as possible to PL/SQL packages. This includes getting http headers (think owa_util) and writing the response (think htp.p). This not only organizes your code better and makes it easier to reuse but it also makes it easier to deploy changes.

Authentication
Think about how you want to authenticate your services before you start. For most B2B applications, OAUTH2 client credentials is sufficient. I have written a post on how to do this ORDS - Securing Services Using OAuth2 (2 Legged). You can also create your own authentication but doing this means you will need to write all your handlers as PL/SQL blocks, check authentication then construct responses for GET services manually (which is a bit of a pain).

Authorization
Don't forget authorization, just because someone has access to your API does not mean they should see all the data. You can use the ORDS metadata to query the client and then decide what data that client should have access to.

Error Messages
Even though Brian talks about this in his paper, it bears repeating. If all goes well, your APIs are going to be used by lots (and lots) of people. Invest time up front in crafting clear error messages that give the developer information on what went wrong, how to fix the problem and a link to the documentation. As developers we often do the 'what went wrong' part but rarely provide the 'how to fix' it advise (and almost never the 'documentation'). Of course, you could shortcut this by just providing your cell number and email address in the error message and have the developer call you directly  :)

For someone who said they were going to leave the advise to someone else, I just gave you a lot of it. Seriously though, I am a big fan of ORDS and think it has enormous (mostly untapped) potential.

Look out for my next post in the 'Back to Basics' Series in October.

DEFINITION
HTTP header fields are components of the header section of the request and response messages in the Hypertext Transfer Protocol (HTTP). They define the operating parameters of an HTTP transaction.

REST SERVICE INVOCATION FLOW
This diagram illustrates (at a high level) the flow of a REST http request from the client to web server on to ORDS and then back to the client.

• The client makes an HTTP request, which is directed, to the application server.
• The application server (and ORDS) routes the request to a specific ORDS REST handler, which is stored in the database.
• The handler executes it's PL/SQL code and passes the response back to the Web Server, which in turn passes it back to the client, completing the loop.

ANATOMY OF A HTTP REQUEST

• HTTP Method
  • In REST, the method indicates what action needs to be performed on the resource. Most commonly this is GET, POST, PUT, DELETE and PATCH.
• Path to Resource
  • This is used to direct the web server to a specific resource / web service.
• Request Headers
  • ​Host: Is the host name of the server where the Web Service is hosted.
  • Authorization: This could be Basic, Bearer, Digest etc.
  • Content Type: Format of payload.
  • Cache-Control: Signifies that the web server should not cache the request.
  • XX-CLIENT-SYSTEM: A custom header to identify the client application.
  • There could be many additional headers
• Request Body
  • This is the payload passed from the client to your web service.

COMMON HTTP HEADERS
There are a number of http headers that are commonly used in the http protocol; these are all accessible from ORDS (assuming that the client application provides them). You don’t need to do anything special to reference these headers except add them to the Parameters section of your REST service (see below for details). A list of the most common HTTP headers is available here: List of HTTP header fields, See Section ‘Request fields’.

ORDS SPECIFIC VARIABLES
There are a number of variables (HTTP Headers and Bind Variables) that are ORDS specific. These fall into the categories of inbound (passed into the handler code block by ORDS) and outbound (populated by your PL/SQL code and passed back to the caller by ORDS).
​
​Input Variables
In ORDS 2.X, ORDS provided a number of X-APEX... headers (X-APEX-BASE, X-APEX-PATH, X-APEX-CHARSET, X-APEX-METHOD and X-APEX-PREFERRED-CONTENT-TYPE). You can refer to my 2014 post for details of these. In ORDS 3.X, most of these are no longer available. Fret not, as this information is still there, you just need to use other methods e.g. OWA_UTIL to access it.

In the spirit of not repeating myself, refer to my earlier post on OWA_UTIL for details of how to access the CGI information for a web service call (Accessing CGI Information from ORDS Handlers). Suffice it to say, all of the information that was available to you with X-APEX variables is still accessible in ORDS 3.X.

Output Variables
​Not much changed on the outbound side, there are three key variables you need to be aware of:

CUSTOM VARIABLES
You are not limited to what ORDS provides (or the HTTP Header Field Standards). You can access custom inbound HTTP headers and also populate your own custom response headers.

Accessing Custom Inbound Headers
Custom headers can be used by client applications to send additional information to your service. For example, let’s say you want all of your clients to pass a string that contains the name of the client system. One-way to do this is to have the client pass a custom http header e.g. XX-CLIENT-SYSTEM

Sample HTTP POST request with Custom header XX-CLIENT-SYSTEM:

In order to access this new header from within your ORDS PL/SQL handler code, you can either use either OWA_UTIL or add a parameter:

Using OWA_UTIL:
  l_client_system := owa_util.get_cgi_env('XX-CLIENT-SYSTEM');

Adding a Parameter:

   l_client_system := :xxClientSystem;
​
In my opinion, adding a parameter is the best way to go as this makes it more obvious to people looking at the code in the future that this parameter is there (and it important to the service). BTW, I am using a convention of prefixing my custom headers with 'XX-' this is to avoid clashing with any future ORDS headers or other industry recognized header names.

​Setting Response HTTP Headers
Looking back at the Parameters in the screen shot above we can see that (other than X-APEX-STATUS-CODE) there is a custom response header called XX-RETURN-MSG. We can set the value of the related bind variable 'xxReturnMsg' in our code and the value will appear in the HTTP header of the response which the client system will be able to read. e.g.
  :xxReturnMsg := 'Error in provided data';

Let's look at a complete example that incorporates all of the above. In this example, I have a module called 'master' with a single template called 'ordsvars' which has just one POST handler.

The handler for the template has 4 parameters:

• XX-CLIENT-SYSTEM (Bind Variable xxClientSystem)
  • Inbound custom HTTP header variable
• X-APEX-STATUS-CODE (Bind Variable xxhttpStatus)
  • This is a standard ORDS response header that sets the HTTP Status Code
  • Set the value of xxhttpStatus to an appropriate response 200, 401 etc.
• DATA (Bind Variable xxResponse)
  • Outbound variable that will contain the response body
• XX-RETURN-MSG (Bind Variable xxReturnMsg)
  • ​A custom HTTP header response variable
    

​Here is the PL/SQL code for the Handler:

I have documented the code to describe how each of the variables and HTTP value are being accesses/set. Now let's see what a call to the above service would look like using PostMan:

Hopefully this helped describe the options available to you when dealing with custom and standard variables available for your use in ORDS.

OWA_UTIL is a database package that provides access to PL/SQL Common Gateway Interface (CGI) variables. Oracle REST Data Services (ORDS) 2.0 introduced a full OWA environment allowing us to tap into the information provided by OWA_UTIL.

So, why is this useful to me? OWA_UTIL holds a significant amount of information about the environment in which your ORDS PL/SQL handler is being called. This includes information such as the character set, host name, port, web service name and even information about the caller of the service such as the user agent.

In this post I will take you through an example of how to tap into the CGI information available to you in OWA_UTIL. Before we get started, however, you should know that the OWA environment is only available for ORDS POST, PUT and DELETE services. The OWA environment is not set for Collection or Query GET handler types.

By far the quickest (and best) way to see what CGI variables are available to ORDS is to use 'owa_util.print_cgi_env'. This will print out all of the CGI variables in the current session. At this point you may ask how do I see this from my ORDS web service? Well it is easier than you may think.

First of all, let's create a basic ORDS module, resource template and GET handler to use for our CGI tinkering. The slide show below, illustrates my example service:

Note: The GET handler Source Type is PL/SQL. Now that we have a get handler, all we need to do it add a PL/SQL block with a call to  owa_util.print_cgi_env:

All that is left to do is call the web service, I am going to illustrate this using CURL:
curl -X GET -H "Accept: application/json" -H "Cache-Control: no-cache" "https://example.com:443/ords/ordstest/master/owablog"

After running the CURL command, you should see a list of the CGI variables, I have a link below to a file with the full response but wanted to illustrate some some of the more interesting ones:

• accept = application/json
• APEX_LISTENER_VERSION = 3.0.5.124.10.54
• REQUEST_METHOD = GET
• PATH_INFO = /owablog
• SCRIPT_NAME = /ords/ordstest/master
• REMOTE_USER = ORDSTEST
• REQUEST_CHARSET = AL32UTF8
• REQUEST_IANA_CHARSET = UTF-8
• REQUEST_PROTOCOL = https
• REQUEST_SCHEME = https
• SERVER_NAME = example.com
• SERVER_PORT = 443

Having this information available opens up some interesting possibilities:

1. You can develop a generic logging API to log web service calls. A combination of PATH_INFO, SCRIPT_NAME and REQUEST_METHOD can be used to identify the service and log all your web service calls to to a log table.
2.  The caller of the service (in this case our CURL command) indicated they wanted a JSON response. Having access to this HTTP header value is key in us being able to honor this response. Of course, in this case we could have added a parameter to our handler to get this HTTP header value from a bind variable.
3. If you have some code that is ORDS version specific, it may be handy to know the version of ORDS which is running.

Now that we know what information we have access to, how do we access it without having to parse the results of 'print_cgi_env'?

The short answer is to use 'owa_util.get_cgi_env'. You simply pass the name of the variable you want and it will return the value. Let's change the Handler for our GET service so that it responds with the URL used to call the web service.

If we run the CURL command again:
curl -X GET -H "Cache-Control: no-cache" "https://example.com:443/ords/ordstest/master/owablog"
We should get the following:
You called me with: https://example.com:443/ords/ordstest/master/owablog