by John Scott

Oracle REST Data Services (ORDS) is the current name for what once upon a time was termed the APEX Listener. Not only has Oracle changed the name, but it has changed a lot of the functionality as well. ORDS is notably different and broader in scope. For example, in ORDS 3 you’ll find the following:

• Installation no longer requires APEX.
• ORDS provides Oracle NoSQL database support.
• ORDS autogenerates REST endpoints for tables and views.

One of the significant changes with ORDS 3.0 is that you no longer require APEX to be installed. When I first learned that, I wondered why you would want to use ORDS without APEX. Well, the simple answer is that Oracle has recognized that not everyone using the Oracle Database has APEX installed; however, they might want to REST enable tables or create manual REST endpoints.

While initially this might look like ORDS is trying to distance itself from APEX, this is actually a positive thing for APEX, since it might introduce a group of people who aren’t already using APEX to the possibility of using APEX. If someone installs ORDS into a database that doesn’t currently have APEX installed and then creates a bunch of REST-enabled services, then the next logical step is to install APEX to be able to provide added functionality (perhaps to manage those REST services, administer data, and so on).

So, don’t worry about the name change. (Anecdotally, a couple of people contacted me around the time HTMLDB was renamed to Application Express and were worried that the name change signaled the end; I’m pleased to report that many years later APEX is stronger than ever.)

ORDS Defined

What is ORDS? Put simply, it’s a Java EE alternative to the OHS or the EPG. In other words, it’s a piece of infrastructure that either provides web server access to your APEX environment (and applications) or, if you’re not using APEX, allows you to create and access your REST services in your database. With ORDS 3, you now have the following deployment options with ORDS:

• Stand-alone
• Oracle WebLogic Server (11g Release 1 [10.3.6] or newer)
• Glassfish Server (Release 3.1.2 or newer)
• Apache Tomcat (Release 7.0.56 or newer)

I could spend an entire book covering these various options, but what I want to concentrate on in this chapter is ORDS itself and not the deployment options (which are well documented in the Oracle documentation). So, in my examples, I will be using the stand-alone configuration, which is not recommended for production usage since it provides no capabilities for automatically restarting in the event of any issues.

What is my recommended production deployment option? Well, I’m afraid the simple answer is (as always!) “it depends.” I think it depends on your current environment, your (company) skill set, and your budget. WebLogic Server is a powerful and configurable option; however, it is the most expensive option, so if you’re not currently licensed for it, then be prepared for your licensing costs to increase (however, with that increased cost comes with increased support). Glassfish is a much cheaper option (or free if using the community edition). However, I think a great combination is ORDS embedded into Apache Tomcat with a reverse proxy Apache server (to help provide capabilities such as virtual hosts, compression, load balancing, intrusion detection, and so on).

ORDS Download and Setup

The ORDS home page is currently located at here:

http://www.oracle.com/technetwork/developer-tools/rest-data-services/overview/index.html

Figure 2-1 shows the download page.

The download is a self-contained ZIP file, which at around 50MB is quite a small download for Oracle software. When you unzip the file, you should end up with a directory structure like this:

[jes@AEMBP ords]$ ls -al
total 199192
drwxr-xr-x   9 jes  staff       306  5 May 10:53 .
drwxr-xr-x  10 jes  staff       340  5 May 10:52 ..
drwxr-xr-x@  3 jes  staff       102  1 May 10:23 docs
drwxr-xr-x@  6 jes  staff       204  1 May 10:28 examples
drwxr-xr-x@  2 jes  staff        68  1 May 10:28 logs
-rw-r-----@  1 jes  staff  55850624  5 May 10:46 ords.3.0.0.121.10.23.zip
-rw-r--r--@  1 jes  staff  46105869  1 May 10:28 ords.war
drwxr-xr-x@  3 jes  staff       102  1 May 10:28 params
-rw-r--r--@  1 jes  staff     23806  1 May 10:28 readme.html

Notice the ZIP file contains documentation and examples, but the main core of the download is the ords.war file (previously called apex.war). During the ORDS configuration you will need to specify the path to the APEX web server files (located in the images directory of the APEX download). These files are not bundled in the ORDS download, so you will need to download them separately.

Note in the following example I have already downloaded the APEX 5 web server files and copied them to a directory, which I will reference during the ORDS installation/configuration.

Running the Install

You can now begin the ORDS installation by running the following command:

java -jar ords.war

You should see the following output:

[jes@AEMBP ords]$ java -jar ords.war
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data:

You need to specify the location where ORDS will store configuration files; this allows you to keep the configuration files in a different location than the main ords.war file so that when you upgrade you don’t need to worry about overwriting the configuration (it also helps to make regressing easier in the case of a failed upgrade).

Enter the location to store configuration data:/u01/ords/ords-config

After entering the path to the configuration directory (which will be created for you), you will be prompted for a number of parameters. Notice that anything in square brackets will be used as a default value, so for example here you override the database server from localhost to aevm:

Enter the name of the database server [localhost]:aevm

If you want to accept the default, just press Enter/Return. In this case, you are using the default listener port of 1521, so you don’t need to override it.

Enter the database listen port [1521]:

Now you specify the database service name or SID.

Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:apex5.localdomain

Then you need to specify the password to be used for the ORDS_PUBLIC_USER account.

Enter the database password for ORDS_PUBLIC_USER:
Confirm password:

Note the username here: ORDS_PUBLIC_USER. This is the clue that ORDS can be used without APEX being installed since all the metadata for ORDS will now be located in the ORDS_PUBLIC_USER schema, and it does not rely on APEX in any way.

The next question can be misleading.

Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step [1]:

When I first saw this, I thought it was referring to the Embedded PL/SQL Gateway; however, if you want to use ORDS to access APEX (rather than using it just for REST enabling your database), then you should choose option 1 here, and you will be prompted for the APEX_PUBLIC_USER passwords.

Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step [1]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:

You will have set these passwords when installing APEX. Refer to the APEX documentation on how to reset them if you have forgotten your choices.

You will also be able to specify passwords for the APEX_LISTENER and APEX_REST_PUBLIC_USER users.

Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
May 5, 2015 11:31:33 AM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults, apex_pu, apex, apex_al, apex_rt
May 5, 2015 11:31:33 AM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 3.0.0.121.10.23
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:

Finally, you need to tell ORDS where it can find the APEX static resources (the JavaScript and CSS files for APEX).

Enter the APEX static resources location:/u01/apex5
Enter the HTTP port [8080]:

Note the path I specified (/u01/apex5) is a directory that contains the images subdirectory from the APEX 5 file download. (In other words, it contains only the images, CSS, and JavaScript, not the SQL files used to install APEX.) Also, you can specify a port to listen on (with the default being 8080, so make sure nothing is already listening on this port if you accept the default).

All being well, you should now see the ORDS server start up.

May 5, 2015 11:31:24 PM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 3.0.0.121.10.23
2015-05-05 11:31:24.405:INFO::main: Logging initialized @13577ms
2015-05-05 11:31:27.528:INFO:oejs.Server:main: jetty-9.2.z-SNAPSHOT
2015-05-05 11:31:27.545:INFO:oejsh.ContextHandler:main: Started o.e.j.s.h.ContextHandler@13c78c0b{/i,null,AVAILABLE}
2015-05-05 11:31:27.638:INFO:/ords:main: INFO: Using configuration folder: /home/oracle/ords-config/ords
2015-05-05 11:31:27.639:INFO:/ords:main: FINEST: |ApplicationContext [configurationFolder=/u01/ords/ords-config, services=Application Scope]|
May 5, 2015 11:31:27 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Validating pool: apex
May 5, 2015 11:31:27 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: apex is correctly configured
May 5, 2015 11:31:27 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Validating pool: apex_al
May 5, 2015 11:31:27 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: apex_al is correctly configured
May 5, 2015 11:31:27 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Validating pool: apex_pu
May 5, 2015 11:31:27 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: apex_pu is correctly configured
May 5, 2015 11:31:27 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Validating pool: apex_rt
May 5, 2015 11:31:28 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: apex_rt is correctly configured
2015-05-05 11:31:33.272:INFO:/ords:main: INFO: Oracle REST Data Services initialized|Oracle REST Data Services version : 3.0.0.121.10.23|Oracle REST Data Services server info: jetty/9.2.z-SNAPSHOT|
2015-05-05 11:31:33.274:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@3dd3bcd{/ords,null,AVAILABLE}
2015-05-05 11:31:33.314:INFO:oejs.ServerConnector:main: Started ServerConnector@73846619{HTTP/1.1}{0.0.0.0:8080}
2015-05-05 11:31:33.315:INFO:oejs.Server:main: Started @17492ms

By default ORDS will be listening on port 8080 (which you can see in the debug information output to the screen). If you want to stop the server, you can simply press Ctrl+C since you are running in stand-alone mode. If at any later point you want to change the configuration, you can do it from the command line, which also provides built-in help by typing the following:

[jes@AEMBP ords]$ java -jar ords.war help

This shows help for each specific option.

[jes@AEMBP ords]$ java -jar ords.war help
java -jar ords.war <COMMAND> [Options] [Arguments]

The following commands are available:

               configdir         Set the value of the web.xml
                                 config.dir property

               help              Describe the usage of this
                                 program or its commands

               install           Installs Oracle REST Data
                                 Services

               map-url           Map a URL pattern to the
                                 named database connection

               nosqladd          Add NoSQL store configuration

               nosqldel          Delete NoSQL store
                                 configuration

               plugin            Package one or more plugin
                                 jar files into ords.war

               set-properties    Edit the value of one or more
                                 configuration settings via
                                 the specified properties file

               set-property      Edit the value of a single
                                 configuration setting

               setup             Configure database connection

               standalone        Launch Oracle REST Data
                                 Services in standalone mode

               static            Generate a Web Application
                                 Archive (WAR) to serve Oracle
                                 Application Express static
                                 resources

               uninstall         Uninstall ORDS_METADATA
                                 schema, proxy user and
                                 related database objects.

               user              Create or update credentials
                                 for a user

To see instructions on how to use each of these commands, type help
followed by the command name, for example

 java -jar ords.war help configdir

If no command is provided Oracle REST Data Services is started in
standalone mode

If everything worked correctly (and assuming you didn’t press Ctrl+C to exit ORDS, or if you did that, you restarted it again with java –jar ords.war), then you should be able to point your browser to point 8080 on whichever machine you installed ORDS on (in my example I installed it to my local machine). Assuming you already had APEX installed, then you should be greeted with the default APEX Application Builder login screen, as shown in Figure 2-2.

Notice the URL contains /ords rather than the /apex you are familiar with from the APEX Listener, or the /pls/apex you’re familiar with from the mod_plsql handler via Apache OHS.

Success! You’re now using ORDS to access APEX 5, so you’re done right? Yes, if all you want to do is use ORDS as a basic web server, you’d done. But it’s capable of much more than that. I’ll cover some of those capabilities in the coming sections, but first I will give you some background on the various database users used by ORDS.

Database Users Used by ORDS

ORDS creates and uses a number of different database users, which can be confusing at first. Part of this complexity is because ORDS can be used without APEX and because APEX itself has a number of different functionalities that may or may not be utilized by ORDS. The following table attempts to give an overview of the types and purpose of each database user:

As you can see, there can potentially be (if you use APEX) a number of different schemas related to APEX and ORDS.

It is also important to know that if you previously defined RESTful web services in your APEX application when using the APEX Listener, then you are able to have those web services migrated to the ORDS schema. The reason for this is consistency; by default, when looking up web service definitions, the ORDS metadata will be used. If the definition cannot be found, then it will look for definitions via the APEX_LISTENER schema (in other words, the pre-3.0 behavior). This gives you the flexibility to use ORDS both with and without APEX, using the new capabilities while maintaining backward capability. In other words, it’s the best of all possible options.

Configuring Multiple Databases

One of the great new features in ORDS is the ability to define multiple database connections, enabling you to have a single server that serves multiple databases (before this capability was available, you would have had to run a server specifically for each database).

When you installed ORDS, an initial database connection was created named apex. To create a new database connection, you can use the following command-line option:

java -jar ords.war setup --database <database name>

This will walk you through the same prompts you saw earlier.

[jes@AEMBP ords]$ java -jar ords.war setup --database demo2
Enter the name of the database server [aevm]:aevm2
Enter the database listen port [1521]:
Enter the database service name [apex5.localdomain]:
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
...<rest of code omitted>

Now that you have defined a second database, you will want to tell ORDS how it should decide to route traffic between the multiple connections.

Defining Request Path Routing

You can configure ORDS to route traffic depending on the request path in the URL, using the map-url configuration option, as shown here:

java -jar ords.war map-url --type base-path --workspace <workspace_name> <path_prefix> <database_name>

where

• workspace is the name of the APEX workspace where the RESTful services are defined for this connection. If you are not using RESTful services, then you can omit this parameter.
• path_prefix is the prefix that must begin at the start of the request path.
• database_name is the name of the database connection you previously added.

So, for example, if you ran the following

java -jar ords.war map-url --type base-path --workspace uat /uat db2

and the ORDS server was running on the aevm hostname, then using the following URL

https://aevm:8080/ords/uat/

would allow you to access the uat workspace in the db2 database (note db2 is a database connection, not really a db2 database!).

Using this technique gives you great flexibility in defining your database connections (and to a certain degree allows some failover and load balancing across multiple ORDS servers).

Defining Request URL Prefix–Based Routing

Similarly to request path routing, you can define request URL prefix–based routing. For example, say you use the following command:

java -jar ords.war map-url --type base-url --workspace uat http://aevm:8080/ords/uat db2

This means that any URL that begins with

http://aevm:8080/ords/uat

will be routed to the db2 database. For example, all the following URLs would be processed by this routing:

http://aevm:8080/ords/uat/f?p=MYAPP
http://aevm:8080/ords/uat/generate_documents
http://aevm:8080/ords/uat/register?p_username=john

Configuration Files

As you have seen, ORDS is pretty straightforward to install and configure. However, what if you want to make a change after you have installed it? First, you will need to know where the configuration files are stored. You can check this by using the configdir command:

java -jar ords.war configdir

This should give you output similar to the following:

[jes@AEMBP ords]$ java -jar ords.war configdir
May 5, 2015 4:17:15 PM oracle.dbtools.cmdline.ModifyConfigDir execute
INFO: The config.dir value is /u01/ords/ords-config

Now you know the configuration files are stored in /u01/ords/ords-config.

Alternatively, if you want to change the location of the configuration files (perhaps you want to test a new configuration without modifying the existing configuration), you can specify a new location after the configdir command.

java -jar ords.war configdir </path/to/new/config>

Configuration File Structure

The configuration directory contains a number of different files contained within a specific structure.

[jes@AEMBP ords]$ pwd
/u01/ords/ords-config/ords
[jes@AEMBP ords]$ tree
.


3 directories, 7 files

The defaults.xml file contains settings that are inherited by all database connections (that is, global settings).

Database-specific configurations are stored in the files conf/<db-name>.xml, so for example in this case you have the conf/apex.xml file, which represents settings specific to the apex database connection.

If you are using APEX (and I’ll assume you are since you bought this book!), then you will have additional configuration files including _al.xml, _rt.xml, and _pu.xml. These files store the configuration details for the APEX_LISTENER, APEX_REST_PUBLIC_USER, and ORDS_PUBLIC_USER.

Configuration File Format

The configuration files are text files in the Java XML properties format, which is fairly easy to read and understand. For example, my defaults.xml file contains the following:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Mon Jun 29 11:33:55 BST 2015</comment>
<entry key="cache.caching">false</entry>
<entry key="cache.directory">/tmp/apex/cache</entry>
<entry key="cache.duration">days</entry>
<entry key="cache.expiration">7</entry>
<entry key="cache.maxEntries">500</entry>
<entry key="cache.monitorInterval">60</entry>
<entry key="cache.procedureNameList"/>
<entry key="cache.type">lru</entry>
<entry key="db.hostname">aevm</entry>
<entry key="db.port">1521</entry>
<entry key="db.servicename">apex5.localdomain</entry>
<entry key="debug.debugger">false</entry>
<entry key="debug.printDebugToScreen">false</entry>
<entry key="error.keepErrorMessages">true</entry>
<entry key="error.maxEntries">50</entry>
<entry key="jdbc.DriverType">thin</entry>
<entry key="jdbc.InactivityTimeout">1800</entry>
<entry key="jdbc.InitialLimit">3</entry>
<entry key="jdbc.MaxConnectionReuseCount">1000</entry>
<entry key="jdbc.MaxLimit">10</entry>
<entry key="jdbc.MaxStatementsLimit">10</entry>
<entry key="jdbc.MinLimit">1</entry>
<entry key="jdbc.statementTimeout">900</entry>
<entry key="log.logging">false</entry>
<entry key="log.maxEntries">50</entry>
<entry key="misc.compress"/>
<entry key="misc.defaultPage">apex</entry>
<entry key="security.crypto.enc.password">UL5dl18_fJOY8rNeQncA..</entry>
<entry key="security.crypto.mac.password">rPP0Yi4KhOerYnV7HH3deQ..</entry>
<entry key="security.disableDefaultExclusionList">false</entry>
<entry key="security.maxEntries">2000</entry>
</properties>

(By the way, I have changed my encrypted password strings in there!)

You can see that it is relatively simple to change the configuration to point at a different hostname using the db.hostname parameter or, for example, turn on the debugger by changing debug.debugger from false to true. A side effect of these configuration files being text files is that it’s easy to take a backup of a configuration file before making any changes and to revert to the old version if you hit any issues.

Configurable Parameters

You can configure pretty much all the parameters in both the defaults.xml file and the <database_name>.xml file. The official Oracle help, currently available at the following link, does a great job of listing each parameter:

http://docs.oracle.com/cd/E56351_01/doc.30/e56293/config_file.htm#AELIG7204

Administration via SQL Developer

If you were familiar with the APEX Listener, then you know that it provided a web-based configuration utility so you could fire up your web browser and configure it without having to resort to the command line. One of the downsides of this, however, was that frequently if the APEX Listener was misconfigured, you were unable to connect to the web front end in order to correct it.

One of the big enhancements with ORDS 3.0 is that you can configure it via SQL Developer 4.1. Do this from the View menu, as shown in Figure 2-3.

You will need to create a new ORDS connection, as shown in Figure 2-4.

This is the piece that can catch you out. I must admit the first few times I attempted this either I missed a section in the documentation or I messed something up. The issue I had was that I didn’t create the user (admin in my screenshot) that would enable me to connect via SQL Developer.

Create via two users via the command line.

java -jar ords.war user dev  "SQL Developer"
java -jar ords.war user admin  "Listener Administrator"

The first command will create a user for developing REST web services via SQL Developer; the second command will create a user that allows you to access (and modify) the ORDS configuration via SQL Developer 4.1.

The important piece here is the section in quotes (for example, SQL Developer and Listener Administrator); those are the roles that the user has. The first time I tried to set up these users, I mistyped the role name and was then frustrated when it did not work. The best advice I can give is to consult the documentation.

Once you have created these users, you should be able to connect via SQL Developer, as shown in Figure 2-5.

This GUI allows you to configure all the parameters that are available in the configuration files I discussed earlier, but obviously in a more user-friendly way. One nice feature of using the GUI as opposed to the command line or editing the configuration files directly is that you can test any configuration changes to ensure the values are correct before uploading them to ORDS server. Figure 2-6 shows the Test Settings menu option, as well as the Upload Settings option.

I won’t go into each setting here because they’re the same as the parameters detailed earlier, but I encourage you to test administration via SQL Developer because it makes the configuration easier and less error prone than directly editing the configuration files.

ORDS Development via SQL Developer

So far you have configured ORDS to act as a regular web server, but you haven’t exploited any of the interesting features in ORDS yet. Let’s change that and dive into creating RESTful web services. You have a couple of options here. You can either REST enable the existing tables and views or create a new REST web service and have complete control over how it behaves.

REST Enabling an Existing Table

First, let’s take a look at how easily you can REST enable an existing table using ORDS. In SQL Developer, you need to connect to the REST Development Wizard (I’m not quite sure if I should call this a wizard or tool, but I’ll stick with wizard for now). Simply select View Rest Data Services Development in SQL Developer, as shown in Figure 2-7.

Once you have connected, you should see a window similar to Figure 2-8.

This is where you can easily create new REST endpoints; however, for a moment, let’s try the simple REST enablement of tables. You might want to do this if you just need basic functionality and quickly want to allow your tables to be queried or modified via REST web service functionality (in practice, I find that I usually want a bit more control or fine-grained access).

First select one of your existing SQL Developer connections and right-click the top-level connection; you should now see a REST Services menu where you can choose to enable or disable at the schema level, as shown in Figure 2-9.

You will be prompted to enter a schema alias (which allows you to hide the underlying schema name) and whether you want to enable authorization. In this example, I will alias my schema to ae and choose that authorization is not required. Once you have enabled REST services, you can right-click the table (or view) and select Enable REST Service, as shown in Figure 2-10.

You will now see a dialog (in Figure 2-11) that enables you to give the object an alias, which is useful if you want to hide the underlying object name, and to enable authorization if required.

One of the nice features here is that the final step of the wizard actually shows you the DDL being executed, which in my example is as follows:

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => ’TRAINING’,
                       p_object => ’EMP’,
                       p_object_type => ’TABLE’,
                       p_object_alias => ’emp’,
                       p_auto_rest_auth => FALSE);

    commit;

END;

So, you can easily enable objects for REST services using the ORDS.ENABLE_OBJECT procedure rather than using SQL Developer. (You might, for example, want to script the enabling and disabling or perhaps provide a front end to do this via an APEX application.)

Invoking the New REST Service

So, what did the previous section give you? Well, you should now be able to call your REST web service using the following uniform resource identifier (URI), where /ae/ represents the alias we used at the schema level and /emp/ represents the alias you used for the table:

http://aevm:8080/ords/ae/emp/

You can use any REST tool to perform a GET on this URI. For example, using the popular tool cURL, you should see the following:

[jes@AEMBP ~]$ curl http://aevm:8080/ords/ae/emp/
{"items":[{"empno":7839,"ename":"KING","job":"PRESIDENT","mgr":null,"hiredate":"1981-11-17T00:00:00Z","sal":5000,"comm":null,"deptno":10,"links":[{"rel":"self","href":"http://aevm:8080/ords/ae/emp/7839"}]},{"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":7839,"hiredate":"1981-04-30T23:00:00Z","sal":90,"comm":null,"deptno":30,"links":[{"rel":"self","href":"http://aevm:8080/ords/ae/emp/7698"}]},{"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"1981-06-08T23:00:00Z","sal":2450,"comm":null,"deptno":10,"links":[{"rel":"self","href":"http://aevm:8080/ords/ae/emp/7782"}]},{"empno":7566,"ename":"JONES","job":"MANAGER","mgr":7839,"hiredate":"1981-04-01T23:00:00Z","sal":2975,"comm":null,"deptno":20,"links":[{"rel":"self","href":"http://aevm:8080/ords/ae/emp/7566
<..rest of output omitted..>

You can see that the output of the curl command is the response from the web service, which is a JSON representation of the EMP table. If you format the output, you can more easily see the way the data is returned.

{
  "items": [
    {
      "empno": 7839,
      "ename": "KING",
      "job": "PRESIDENT",
      "mgr": null,
      "hiredate": "1981-11-17T00:00:00Z",
      "sal": 5000,
      "comm": null,
      "deptno": 10,
      "links": [
        {
          "rel": "self",
          "href": "http://aevm:8080/ords/ae/emp/7839"
        }
      ]
    },
    <...other records omitted..>
    {
      "empno": 7934,
      "ename": "MILLER",
      "job": "CLERK",
      "mgr": 7782,
      "hiredate": "1982-01-23T00:00:00Z",
      "sal": 1300,
      "comm": null,
      "deptno": 10,
      "links": [
        {
          "rel": "self",
          "href": "http://aevm:8080/ords/ae/emp/7934"
        }
      ]
    }
  ],
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "count": 14,
  "links": [
    {
      "rel": "self",
      "href": "http://aevm:8080/ords/ae/emp/"
    },
    {
      "rel": "edit",
      "href": "http://aevm:8080/ords/ae/emp/"
    },
    {
      "rel": "describedby",
      "href": "http://aevm:8080/ords/ae/metadata-catalog/emp/"
    },
    {
      "rel": "first",
      "href": "http://aevm:8080/ords/ae/emp/"
    }
  ]
}

You get an items array containing each record as a JSON object together with some summary information to tell you how many rows were returned (the count property) and some standard REST attributes to represent links back to this object (which can in other cases be used to page through resultsets, and so on).

Now you may be thinking, “What use is this data in JSON format?” Well, the answer is that with either Oracle 12c or APEX 5 it has become much easier to parse and query JSON data than ever before. It’s not the purpose of this chapter to go into detail on the new 12c or APEX 5 JSON functionality; however, using JSON is increasingly common among front-end web development. If you ever use third-party jQuery plugins, for example, or D3 visualizations, then the data typically needs to be provided in JSON format. Using ORDS to provide a REST web service in JSON format makes it incredibly simple to generate the data in the format you need.

Creating a REST Web Service

So, that was a simple REST-enabled table; let’s take a look at creating a REST web service manually. Recall earlier you connected to the REST Development Wizard in SQL Developer. Now go back to that region and right-click the Modules link, which should allow you to select New Module, as shown in Figure 2-12.

You need to provide a module name as well as a URI prefix (note the trailing slash in the URI prefix), as shown in Figure 2-13.

I can’t tell you how many times I forgot to select the Publish check box, only to be frustrated when my simple web service didn’t work. So if you find yours doesn’t work, edit your module and make sure you actually enabled it by selecting the Publish check box. (This provides a simple mechanism to configure your web services and to disable them without having to lose all the details.)

Now you need to provide the URI pattern that will be used to map to this web service. Notice in Figure 2-14 it shows the full URL combining the paths I used in step 1 and step 2 of the wizard so that you know how to call your web service after creation.

You can also specify an entity tag on this step of the wizard. This allows you to optimize the web service call with a version identifier so that you can avoid retrieving previously retrieved resources. If you are not sure how to specify an ETag stick, with the default option of Secure Hash, it will be calculated for you.

The final step of the wizard is to specify the Method and Source Type settings, as shown in Figure 2-15, as well as the format the data should be returned in and whether the URI requires secure access. (Please consult the Oracle documentation on configuring secure access because there is a lot of detail that I cannot cover in a single chapter.)

If you are unfamiliar with web services, there are the following method handlers:

• GET
• POST
• PUT
• DELETE

Think of the GET as analogous to a SQL INSERT statement, the POST as an INSERT statement, the PUT as a MERGE statement, and the DELETE as a—well you guessed it—a DELETE statement. In reality, you could handle these in any way you wanted, but that is a good general convention to think of these operations.

The Source Type field has the following options:

• Feed: This executes a SQL query and returns results in a JSON feed representation.
• Media Resource: This executes a SQL query and returns a binary representation with an HTTP Content-Type header to specify the type of the returned content (useful for downloading images or documents, and so on).
• PL/SQL: This executes an anonymous PL/SQL block with optional IN and IN/OUT parameters that accept and return a JSON representation.
• Query: This executes a SQL query and returns the results in JSON or CSV.
• Collection Query: This is currently not documented in the help documentation. Please consult the documentation because this might have changed following the publication of this book.
• Collection Query Item: This is currently not documented in the help documentation. Please consult the documentation because this might have changed following the publication of this book.

In this example, you will choose the Query option (which together with PL/SQL Method is probably the most common choice you’ll pick).

The UI allows you to define the query you will use with the familiar SQL Developer worksheet, as shown in Figure 2-16.

Notice you have referenced a parameter here using the :BIND notation, so you need to specify how the parameter is passed, as shown in Figure 2-17.

In this example, you will pass the parameters as HTTP headers. This screen allows you to map from an HTTP header variable to an internal parameter that you can use in the query or PL/SQL procedure, and so on.

Now you can test your new web service, but one important thing to remember is that whenever you create or modify a module or resource template in SQL Developer, you need to validate and re-upload it to ORDS.

Now if you rerun the curl command, you should see the following:

[jes@AEMBP ~]$ curl http://aevm:8080/ords/training/ae/employees
{"items":[]}

Note the empty return result (in other words, an items array with zero elements). This is because you added the following predicate to your query, but you have not passed a department to the web service:

where e.deptno = :DEPT

You can pass a department number in the HTTP header in cURL using the following command:

[jes@AEMBP ~]$ curl -H "DEPT:10"  http://aevm:8080/ords/training/ae/employees
{
    "items": [
        {
            "deptno": 10,
            "empno": 7839,
            "ename": "KING",
            "hiredate": "1981-11-17T00:00:00Z",
            "job": "PRESIDENT",
            "sal": 5000
        },
        {
            "deptno": 10,
            "empno": 7782,
            "ename": "CLARK",
            "hiredate": "1981-06-08T23:00:00Z",
            "job": "MANAGER",
            "mgr": 7839,
            "sal": 2450
        },
        {
            "deptno": 10,
            "empno": 7934,
            "ename": "MILLER",
            "hiredate": "1982-01-23T00:00:00Z",
            "job": "CLERK",
            "mgr": 7782,
            "sal": 1300
        }
    ],
    "next": {
        "$ref": "http://aevm:8080/ords/training/ae/employees?page=1"
    }
}

You can see how easily you can now construct web services that can be called from any other systems to either pull data from your database or perhaps create or modify data.

Let’s look at how you can create a web service that allows users to update the salary for an employee. First create a new handler called employee with a POST method (with a Source Type setting of PL/SQL), as shown in Figure 2-18.

Next you need to write the PL/SQL code for the web service (in the SQL worksheet like you did before).

begin
  update emp e
     set e.sal   = :SAL
   where e.empno = :EMPNO;
end;

Then you need to define the parameters, as shown in Figure 2-19.

After uploading the changes back to ORDS, you can call the web service using cURL.

[jes@AEMBP ~]$ curl -X POST -H ’EMPNO:7839’ -H ’SAL:30000’ http://aevm:8080/ords/training/ae/employee
[jes@AEMBP ~]$

You will notice you do not get any response (you could modify the PL/SQL routine to echo the updated employee record). However, if you query the emp table in your database, you should see that the employee with empno 7839 now has a salary of 30000.

If you wanted to retrieve an image of the employee from the database, then the Media Resource source type is a good option to choose. In this case, you need to provide a SQL query that returns exactly one record, and the query needs to return both the content_type and the content_body, similar to the following query:

select content_type,
       content_body
  from your_table
 where ...

Accessing Hidden Parameters

Depending on the type of URI you have defined, there may be some hidden parameters that are automatically defined for you so that you can use them inside your logic. For example, in a PL/SQL handler, you can access the BODY of the POSTed data using :body, so you could have a block of PL/SQL like this:

declare
  l_clob         clob;
  l_src_offset   number;
  l_dest_offset  number;
  l_blob_csid    number := dbms_lob.default_csid;
  v_lang_context number := dbms_lob.default_lang_ctx;
  l_warning      number;
  l_amount       number;
begin
  dbms_lob.createtemporary(l_clob, true);

  l_src_offset     := 1;
  l_dest_offset := 1;
  l_amount := dbms_lob.getlength(:body);

  dbms_lob.converttoclob(l_clob,
                         :body,
                         l_amount,
                         l_src_offset,
                         l_dest_offset,
                         l_blob_csid,
                         v_lang_context,
                         l_warning);

  insert into mytable(
    id,
    data
  ) values (
    mytable_seq.nextval,
    l_clob
  );

end;

This block would convert the body of the POSTed data from a blob to a clob and store it in a table. Note that the purpose of this code is to show how you would reference the body rather than show a full end-to-end demonstration.

Accessing ORDS Web Services via APEX

Although this chapter is primarily about installing and configuring ORDS, I thought it would be useful to show just how easily you can access your ORDS web services from an APEX application. First, though, why would you want to do that? Well, recall that earlier I mentioned that most front-end web development requires that you will run into using JSON data at one point or another, typically if you are trying to integrate a jQuery plugin or perhaps a third-party visualization library such as D3.

Without getting into the specifics of an actual jQuery plugin, let’s imagine two use cases.

• From APEX you want to access an ORDS web service via PL/SQL.
• From APEX you want to access an ORDS web service via jQuery.

In both cases the web service could be defined in the local ORDS server (that is, the one you’re accessing APEX via) or in a remote web service.

In the PL/SQL method, you can use the APEX_WEB_SERVICE API and call the MAKE_REST_REQUEST procedure like this:

declare
    l_clob CLOB;
begin
    l_clob := apex_web_service.make_rest_request(
        p_url => ’http://aevm:8080/ords/training/ae/employees’,
        p_http_method => ’GET’);

   sys.htp.p(l_clob);
end;

Here you use the sys.htp.p command to output the response, but you could assign it to a variable, capture it in a table, and so on. If you wanted to pass some parameters to the web service, you could pass those via the p_parm_name and p_parm_value parameters. Here’s an example:

declare
    l_clob       CLOB;
begin
    l_clob := apex_web_service.make_rest_request(
        p_url => ’http://aevm:8080/ords/training/ae/employees’,
        p_http_method => ’GET’,
        p_parm_name  => apex_util.string_to_table(’param1:param2’),
        p_parm_value => apex_util.string_to_table(’value1:value2’)););

   sys.htp.p(l_clob);
end;

As you can see, it’s pretty simple to call a web service via PL/SQL from APEX. If you want to do it via jQuery, there are a few different ways to do it, but typically I tend to use the $.ajax method. Here’s an example:

$.ajax({
  method: "GET",
  url: "/ords/training/ae/employees"
})
  .done(function( data ) {
    console.log(data.items.count);
    for (var i = 0; i < data.items.length; i++) {
        emp = data.items[i];
        console.log(emp.ename);
    }
  });

This will call the /ords/training/ae/employees URI on the same web server you are accessing APEX. When the web service response is returned, then you loop around the items (recall the array of items you saw earlier) and output the employee name.

Now with a web service and a few lines of code, your APEX application is able to query, create, and modify data easily in another Oracle Database using Oracle Rest Data Services as the interface.

There are so many more features available in ORDS (I’ve barely scratched the surface), I could spend an entire book on it; however, I have only a chapter. I do encourage you to look at the documentation that goes into far greater detail than I can here, as well as touching on some of the more specific features (for example, JSON query filters).

Summary

ORDS represents a new step in Oracle’s evolution of the listener used with APEX. ORDS is more powerful than previous iterations and is more generally useful beyond merely for Application Express.

You learned in this chapter how to download ORDS and install it and then to configure it for your needs. You were also introduced to the administration interface that is now accessible via SQL Developer. Finally, you saw how to REST enable existing tables and how to create REST endpoints and service requests made to them.