Intoduction to Oracle Rest Data Services

Cyrille Modiano

OCP Oracle DBA in Montreal at DBSoft

Latest posts by Cyrille Modiano (see all)

Presentation

First, let’s talk about REST apis. REST apis are very popular nowdays because they are easily accessible from any client able to make http(s) requests. The beauty of this is that you can access different systems through these rest apis independantly of how the system is built in the backend. For example I can access with a simple curl command the twitter, youtube or any public api which will return me data.

Rest APIs usually present data as a JSON object. If your are not already familiar with JSON here is an example of a JSON object:

Oracle 12.1.0.2 introduced the support of JSON as a datatype and methods to query JSON objects. This make things a lot easier when you have to work with these data structures.

What the point of using a REST API for a database?

Activating a REST service for your database gives you the possibility to access and modify your data directly through a simple http(s) request which is an easy way to give access to your data for an existing application.

Using REST also alow to create a clear separation between database development and software development. Most of the time, when a software developer wants to use a Database through an application he will have to learn SQL and know the particularity of database specific functions, views, and procedural language. He also have to use specific drivers to access the database and learn how they work. Using REST, the software developer only have to call a web service when he wants to interact with the database which simplify his life, leaving people with database knowledge develop the service they will call.

REST

ORDS is free of charge and can be deployed on WebLogic, Tomcat and Glassfish application servers or as a standalone service.

Installing and configuring Oracle Rest Data Services

Install

First you will need to download ORDS from the following link:
Download

In this post I will deploy ORDS as a standalone service running directly on the database server. Like explained earlier, once configured ORDS can also be deployed on a compatible application server.

Upload the zip file to your server and unzip it to a temprary directory:

Configure

Start the configuration using:

Here I chose not to use APEX and run the ORDS as a standalone service. To keep things simple I also used HTTP over HTTPS:

Now the service is started and you should be able to access it through a web browser. If you access to http://<ip_or_host_configured>:8080/ords.
You should have the following page:

RESTThe 404 is completly normal.

Getting Started

Now that we have configured ORDS, why not enable some services to access our data?
For my tests I will use the Oracle sample schema HR. The first thing we need to do is enable Restful services for the schema, this is done through the ORDS package:

The url_mapping_pattern for the BASE_PATH describe the url structure necessary to access the services served by this schema. Now we are all set to create our first service.
Here I will create a service to query my EMPLOYEES table:

This service is now accessible through the following url : http://<ip_or_host_configured>:8080/ords/hr/employees/GetAllEmployees/
If you try to access this url through a web browser, you should obtain something like this:

Rest sample service
We have created a simple web service to access our table, now let’s go a little bit further and try to add a filter. We define the following service:

I will now try to access an employee based on the employee_id by using the following url: http://<ip_or_host_configured>:8080/ords/hr/employees/GetEmployee/102
And I get the following result:

Rest service

We just saw how to interact with a specific row, here we selected a row but this could have been a delete statement as well. Now let’s be a little bit more ambitious, let’s say an application wants  to send us data using our REST api, the data we will receive will be a JSON object containing the definition of the row(s) we will need to insert. For that purpose we will create a stored procedure to process the incoming data and insert it into the EMPLOYEES table. This time we will not use a GET but a POST message. By default ORDS treats the incoming data of a post as a blob object. In order to use the data we will need to convert it as clob. Here is the procedure:

Now we need to create a service which will expose this procedure publicly. Take good attention to the type declared in the service:

Here we use ORDS.source_type_plsql because we call a PL/SQL procedure.
The :body variable is the default variable for data passed through a POST request to the REST service.

Let’s try to insert an employee, I will create a JSON file with the following content in /tmp:

Let’s try to post ou data to the service. Here I will use curl with the following command:

and the output is:

An http 200 means everything went well, here is the verification:

Here we just submited one employee, let’s try 3 at a time with the following JSON:

This is the end of this introduction. Hope it helped you see the capabilities of Oracle Rest Data Services.

See you.

6 thoughts on “Intoduction to Oracle Rest Data Services

  1. Hi guy ! Thank you for this cool tutorial, do you know if it’s possible to reply with other formats, like xml, or others ? ^^
    Thanks in advance

    1. Hi Vincent,

      Thank you for reading my blog. I Know you can return csv by using:
      p_source_type => ORDS.source_type_csv_query
      For XML, I found that procedure:

      CREATE OR REPLACE PROCEDURE P_SHOW_XML_TABLE
      IS
      l_clob CLOB;

      BEGIN
      SELECT XMLELEMENT("article",
      XMLFOREST( e.id as "id",
      .....
      )
      ).getClobVal()
      INTO l_clob
      FROM article e
      where rownum = 1;

      OWA_UTIL.mime_header('text/xml');
      HTP.print(l_clob);

      EXCEPTION
      WHEN OTHERS THEN
      raise;
      END P_SHOW_XML_TABLE;

      Then just create your service and pass this newly created procedure in the p_source parameter:

      BEGIN
      ords.create_service (
      p_module_name => 'test.xmltable' ,
      p_base_path => '/test/',
      p_pattern => 'xmltable/',
      p_method => 'GET',
      p_source_type => ords.source_type_plsql,
      p_source => 'begin p_show_xml_table; end;'
      );
      commit;
      END;

      I found this example Here

      Thanks

      Cyrille

  2. Thank you Cyrille it’s a very good and clear introduction.
    It’s been a long time since I wanted to know how REST was used with Oracle. I see now many applications.
    Have a nice day my friend!

Leave a Reply