Consuming RESTful Web Services in Oracle Database Cloud Service using PL/SQL

Introduction

Oracle Database Cloud Service with the Database as a Service option comes preconfigured with Oracle APEX and Oracle REST Data Services. These features can be used to easily create and interact with RESTful Web Services. This example is based on a specific customer requirement, however it can be useful across a range of options with RESTful Web Services. The customer had the task at hand to access RESTful Web Services using PL/SQL. The main reason for this was being able to integrate the RESTful Web Services in their existing PL/SQL applications and use them across a range of other Java Applications without the need to create a separate interface. This example is based on the RESTful services that have been created as part of this post.

Web Service Creation

Follow the steps outlined in this post. For this example you will need to create the GET and POST Resource handlers.

Network Configuration

In order to access the RESTful Web Service the traffic to port 80 has to be enabled for the Database Cloud Service Instance. For this example traffic will only be allowed from a specific IP address. To achieve this navigate the Oracle Compute Cloud Service Console – click on the Network tab and click the Security IP Lists button on the left hand side.

Clicking the “Create Security IP list” button opens the a dialog where a meaningful name and desription plus the IP address that will access the service needs to be entered. Finish by clicking “Create”.

The next step is to create a Secuity list. Select the “Security Rules” button on the left hand side and click the “Create Secuity Rule” button on the right.

This opens a dialog where a Name needs to be entered. Make sure to select “Enabled” for status and select the previously created Security IP List as source and select your DBCS instance as Destination. For this example select the out-of-the-box Security Appliaction http – this will open up Port 80. Finish by clicking “Create”.

For easier access to the database I also created a rule to allow traffic on port 1521. Security application: ora_dblistener (Port 1521). This allows to directly access the database from the target IP.

Retrieving Data: GET via PL/SQL

Accessing the RESTful Web Service is straight forward. Make sure to replace the <dbcs-ip> tag with the public IP of your Database Cloud Service Instance. This public IP can be found on the Overview Page of the Oracle Database Cloud Service Console.

The central function used for interaction with RESTful Web Service is apex_web_service.make_rest_request. Please note that there is also a function called  apex_web_service.make_request which is similarly used for interaction with SOAP Web Services. Be aware that the return type is CLOB in JavaScript Object Notation (JSON) and might need to be parsed for your particular use case.  A great tutorial how to achieve this can be found here.

set SERVEROUTPUT ON

declare

  v_result clob;

begin

  v_result := apex_web_service.make_rest_request(

    p_url            => 'http://dbcs-ip/ords/pdb1/restful/person/persons/'

   ,p_http_method    => 'GET'

    );

   dbms_output.put_line(v_result);

end;
/

This will return the data that has previously been created in the example here. Using SQL*plus will print the returned JSON via the dbms_output.put_line procedure.

Submitting Data: POST via PL/SQL

apex_web_service.make_rest_request also allow to submit data into a RESTful Web Service using the POST  method. The following script allows using the POST Resource Handler to submit data into the Instance. apex_web_service.g_request_headers allows submitting headers as part of the request.

set DEFINE off

set SERVEROUTPUT ON

declare

  v_result clob;

  v_lastname varchar2(20);

  v_firstname varchar2(20);

  v_email varchar2(40);

  v_phone_number varchar2(20);

  v_body clob;

begin

  v_lastname := 'Koenn';

  v_firstname := 'Roland';

  v_email := 'mail@oracle.com';

  v_phone_number := '1112223333';

  v_body := to_clob('LASTNAME='||v_lastname||'&FIRSTNAME='||v_firstname||'&EMAIL='||v_email||'&PHONE_NUMBER='||v_phone_number);

  apex_web_service.g_request_headers(1).name := 'Content-Type';

  apex_web_service.g_request_headers(1).value := 'application/x-www-form-urlencoded';

   v_result := apex_web_service.make_rest_request(

    p_url            => 'http://<dbcs-ip>/ords/pdb1/restful/person/persons/'

   ,p_http_method    => 'POST'

   ,p_body =>  v_body

   );

   dbms_output.put_line(v_result);

end;

/

 After submitting the above call the data can be verified be using the GET method – same as previously:

Or alternatively we can query the target table directly – for example using the APEX GUI.

Further Reading

Application Express API Reference - APEX_WEB_SERVICE
https://docs.oracle.com/database/121/AEAPI/apex_web_service.htm 

Oracle REST Data Services Documentation
https://docs.oracle.com/cd/E56351_01/index.htm