Subscribe

Share

Database, SQL and PL/SQL

Automatic REST

REST-enable your Oracle Database tables and views with Oracle REST Data Services and Oracle SQL Developer.

By Jeff Smith

March/April 2017

Representational State Transfer (REST) is today’s dominant software architectural style for creating modern, scalable web services. JavaScript Object Notation (JSON) is the most popular data interchange format for RESTful web services (which use the REST architecture).

Oracle REST Data Services accepts RESTful web service uniform resource identifiers (URIs) and directs them to the appropriate SQL statement or PL/SQL block, returning the output in either JSON or comma-separated values (CSV) format. Oracle REST Data Services 3.0 is easy to install and configure, empowers autogenerated REST endpoints for tables and views, and is supported in Oracle SQL Developer 4.1.

This article will show you how to REST-enable a table and how to query, insert, and update records in it and delete records from it via REST requests—all without writing any custom SQL.

To follow along, you will need to install and configure Oracle REST Data Services with your Oracle Database instance. You will also need a browser and a RESTful client capable of making REST calls. All of this is available via the Oracle Technology Network Developer Day Database Virtual Box Appliance—it includes Oracle Database 12c Release 1, Oracle REST Data Services 3.0, and Oracle SQL Developer 4.1 configured and running. The installed appliance appears as a virtual desktop, as shown in Figure 1.

o27sqldev-f1
Figure 1: Oracle Technology Network Developer Day Database Virtual Box Appliance

For those who have never worked with an Oracle VM VirtualBox appliance before, I’ve written this quick introduction for getting started with virtual appliances and connecting to Oracle Database 12c.

Confirming That Oracle REST Data Services Is Running

In addition to marshaling REST requests to the database and returning results as JSON to the requester, Oracle REST Data Services can also run as a standalone web server.

To see the web server in action, in the appliance, open a browser and request the following URL from Oracle REST Data Services:

http://localhost:8080/ords/hr/

Oracle REST Data Services is listening on port 8080, and you’re asking for a RESTful endpoint on the HR schema. Because you have not REST-enabled HR yet, the link should return a 404 error, with a message indicating that the resource, “hr,” could not be mapped to a database object.

If you instead get a request timeout, Oracle REST Data Services is not running. If you have just started up the appliance, you may need to wait a few more moments for the database and Oracle REST Data Services to finish their startup processes. You can also start Oracle REST Data Services in the appliance with this command:

/home/oracle/java/jdk1.8.0_91/bin/java -Xmx1024m 
-Xms256m -jar /home/oracle/ords/ords.war
REST-Enabling a Schema

Before making a table or a view available via REST, you must first enable and map its schema. Use Oracle SQL Developer to REST-enable the HR schema, which is already available and unlocked in the appliance database.

To REST-enable the HR schema, first create a connection in Oracle SQL Developer as HR, with a password of oracle to the orcl service on locahost:1521.

After your connection has been created, right-click the connection in the tree and select REST Services -> Enable REST Services.

You do not want to expose the Oracle HR username via the REST URI, so you will be creating the alias “peeps” for HR, as shown in Figure 2. Enter peeps for Schema alias, check Enable schema, and click Next. For the sake of simplicity and the space constraints of this article, you will not be securing this schema or the REST-enabled table, but it is highly advisable always to do so in your own environments. On the next wizard screen, click Finish.

o27sqldev-f2
Figure 2: REST-enabling a schema

Oracle SQL Developer provides a complete integrated development environment (IDE) for managing your RESTful services and REST-enabling your database objects, and Oracle REST Data Services also provides a complete PL/SQL API. So the same operation in the RESTful Services wizard that REST-enabled the HR schema can also be accomplished via this code block:

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                      p_schema => 'HR',
                      p_url_mapping_type => 
                      'BASE_PATH',
                      p_url_mapping_pattern => 'peeps',
                      p_auto_rest_auth => FALSE);
    commit;
END;

With the HR schema REST-enabled, you can start REST-enabling its tables and views. For the remainder of this article, you will be working with the LOCATIONS table.

Back in Oracle SQL Developer, find the LOCATIONS table in your connection tree. Right-click it, and again select REST Services -> Enable REST Services.

In the dialog box, check Enable object, enter places as an alias, and leave the authorization required item unchecked. This directs Oracle REST Data Services to create the alias “places” for the LOCATIONS table.

Click Next, and observe the PL/SQL required to REST-enable the LOCATIONS table in the following dialog box. Click Finish.

Now that both the HR schema and the LOCATIONS table have been REST-enabled, you can start making REST requests.

GETs/SELECTs

The automatic REST enablement feature in Oracle REST Data Services provides a complete create, retrieve, update, delete (CRUD) API on your REST-enabled objects.

To see the API in action, issue an HTTP GET request on the “places” endpoint to return a JSON document that delivers the results of your SELECT query on the LOCATIONS table.

You can use a RESTful client or CURL (a command-line tool) to make this request, but you can also point to the resource in your browser, which defaults to GET:

http://localhost:8080/ords/peeps/places

Making this request in Firefox running in the appliance to the LOCATIONS table looks like Figure 3.

o27sqldev-f3
Figure 3: Generated automatic REST endpoints, accessible via /ORDS/<schema>/<table>/

Because you requested /places/, you are asking to do a GET (SELECT) of all the records in the LOCATIONS table. Note that one of the requirements for a table to be REST-enabled is that it have a primary key defined. In Figure 3, observe how each record is addressable by its primary key (location_id) value.

Automatic REST points are resources that can handle all REST requests (GET, POST, PUT, DELETE) automatically. And automatic REST points on your tables do more than just return all records or a specific record. You can also apply sorts and predicates to your automatically generated SELECT statements. These amendments to your GET request are passed along via a ?q=<FilterClause> appended to the table URI, where FilterClause is a JSON document describing the WHERE and/or ORDER BY clause.

The complete syntax rules for these amendments are listed in the REST Data Services Installation, Configuration, and Development Guide. To see an example of a predicate with an ORDER BY clause, request LOCATIONS with a LOCATION_ID of “US” ordered by STATE_PROVINCE in ascending order.

Again in the Firefox browser, ask for this URI to localhost:8080/ords:

/peeps/places/?q={"country_id":{"$eq":"US"},"$orderby": 
{"STATE_PROVINCE":"ASC"}}

The records satisfying the predicate are returned in the expected order, as shown in Figure 4.

This URI is mapped by Oracle REST Data Services to the HR schema and the LOCATIONS table, and the WHERE and ORDER BY SQL clauses are generated automatically. The application developer needs to know only the resources available and the expected syntax of the JSON describing the query filters.

o27sqldev-f4
Figure 4: Locations in the US ordered by STATE_PROVINCE

POSTs/INSERTs

Issuing POST requests requires a RESTful client. The appliance includes one conveniently located on the desktop, “Rest Client.” Open it.

To insert a record, issue a POST to /ords/schema/table/ with a content type of “application/json” and put the record to be inserted into the body of the request in JSON format.

The record in JSON format is simply the list of column names and values to be inserted. Inserting the address of the world’s most famous detective would look like this:

{"location_id":3300,"street_address":"221B Baker Street",
"postal_code":"NW1 6XE","city":"London","country_id":"UK"}

Issuing this POST to /ords/peeps/places/ will insert a new record whose primary key is 3300.

In the RESTful client, set METHOD to “POST”; paste the JSON into the body, remembering to set the content type to “application/json”; and, for the URL, provide the RESTful endpoint for the LOCATIONS table, http://localhost:8080/ords/peeps/places/.

Click the double-green-arrow button to submit the request. Observe the Body panel in the output, as shown in Figure 5. (Note that I have used the RESTful client’s JSON Formatter feature to make the JSON easier to read. Oracle REST Data Services does not format the JSON responses.)


o27sqldev-f5
Figure 5: The response to the POST request

The response is a 200 (all is good!) followed by the inserted record and how to access it via REST. The documentation includes the complete process for inserting records via the automatic REST endpoints generated by Oracle REST Data Services when you REST-enable a table.

PUTs/UPDATEs

The process for updating a record is much like the process for an INSERT. The two key differences are that you do a PUT versus a POST and that you specify the record to be updated in the URI—for example, ords/<SchemaAlias>/>ObjectAlias>/<KeyValues>.

So again using the RESTful client, switch the method to PUT. You will update the record you just inserted with a different street address. Do a PUT of the following to /ords/peeps/places/3300:

{"location_id":3300,"street_address":"221B Baker Street c/o Worlds Best Det.",
"postal_code":"NW1 6XE","city":"London","country_id":"UK"}

Figure 6 shows the result. (Note that I have again used the RESTful client’s JSON Formatter feature to make the JSON easier to read. Oracle REST Data Services does not format the JSON responses.)


o27sqldev-f6
Figure 6: The response to the PUT—the new record along with the URI to access it

DELs/DELETEs

The process for a delete is extremely simple: issue a DEL on the record URI.

Before you delete the record, let’s confirm that it exists in the table. Run

SELECT * FROM HR.LOCATIONS WHERE LOCATION_ID = 3300; 

You should see the record you previously inserted and updated.

Now let’s issue the DEL, as shown in Figure 7.


o27sqldev-f7
Figure 7: The response confirming that one row was deleted

Because the row is gone, the response has no URI by which to reference the now-deleted record. If you rerun the earlier SELECT query in Oracle SQL Developer, you should get no rows returned for LOCATION_ID 3300.

REST is stateless—there is no transaction model. Your INSERTs/UPDATEs/DELETEs are autocommitted.

Summary

In this article, you REST-enabled a schema and a table. You also queried a table and inserted, updated, and deleted a record.

Note that REST-enabling a resource in the database without protecting it via authentication is not recommended; everyone with access to the URI can now do anything they want to your table or view. The “Securing Your RESTful Service in Oracle REST Data Services” lab, included with the virtual appliance, shows how to secure your REST-enabled objects with third-party Initiative for Open Authentication (OATH) 2.0 authentication.

The automatic REST enablement feature of Oracle REST Data Services 3.0 provides a REST API to your tables and views, but this may not be sufficient for your application. You can also write and publish custom RESTful services by using Oracle SQL Developer and Oracle REST Data Services to run any SQL or PL/SQL block via REST. The REST developer hands-on lab, included with the virtual appliance, demonstrates how to develop and deploy a custom RESTful service.

Next Steps

DOWNLOAD Oracle Technology Network Developer Day Database Virtual Box Appliance.

GET a quick introduction to Oracle Technology Network Virtual Box appliances.

READ the REST Data Services Installation, Configuration, and Development Guide.

 

Photography by Meric Dagli, Unsplash