Oracle REST Data Services – Create Web Services

In my previous blog Oracle REST Data Services – Installation and Configuration :

• We have installed ORDS in standalone mode.
• Configured ORDS to be administer by SQL Developer

In this blog, we will explain how to create simple RESTful Web Services using PL/SQL and a browser Rest Client..

My sources are :

• Oracle REST Data Services documentation.
•  Thatjeffsmith.
• oracle-base.

First, let’s try to REST the schema/tables “HR/EMPLOYEES” using the Auto REST feature.

Auto REST feature allows to interact with schema/tables without writing any code.

The first step is to enable the REST Services for the schema HR :

In a productive mode, for security reason, “Schema alias” must be changed and “Authentication required” must be checked.

Let’s see the content of SQL code generated (taken from the SQL tab), you can execute this piece of code instead using graphical interface :

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'hr',
                       p_auto_rest_auth => FALSE);

    commit;

END;

The second step is to enable the REST for Table Employees :

The SQL code related is :

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_object => 'EMPLOYEES',
                       p_object_type => 'TABLE',
                       p_object_alias => 'employees',
                       p_auto_rest_auth => FALSE);

    commit;

END;

Now let’s try to access the EMPLOYEES table via /ords/hr/employees :

Let’s just explain how it works :

1. We do GET on employees table, ORDS will find the appropriate database workload (here the SQL command : “SELECT * FROM EMPLOYEES”).
2. ORDS return the output as formatted JSON (see the output above).

Let’s try now to create RESTful Web Services using PL/SQL:

Let’s create a REST Web Services using the GET method (Read/SELECT):

BEGIN
  ORDS.define_service(
    p_module_name    => 'rest-v1',
    p_base_path      => 'rest-v1/',
    p_pattern        => 'countries/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM countries',
    p_items_per_page => 0);

  COMMIT;
END;
/

Now let’s try to access the COUNTRIES table via /ords/hr/rest-v1/countries:

Let’s check the content of USER_ORDS_% views :

SELECT id, name, uri_prefix
FROM   user_ords_modules
ORDER BY name;

        ID NAME      URI_PREFIX                                                                                                                                                                                                                                                     
---------- ---------------------
     10060 rest-v1   /rest-v1/                                                                                                                                                                                                                                                      


SELECT id, module_id, uri_template
FROM   user_ords_templates
ORDER BY module_id;
        ID  MODULE_ID URI_TEMPLATE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
---------- ---------- ------------
     10061      10060 countries/                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              



SELECT id, template_id, source_type, method, source
FROM   user_ords_handlers
ORDER BY id;

        ID TEMPLATE_ID SOURCE_TYPE          METHOD     SOURCE                                                                          
---------- ----------- ---------------------------------------------------------
     10062       10061 json/collection      GET        SELECT * FROM countries

Now let’s create a POST Web Services (Create/INSERT):

First create a PL/SQL procedure to create new rows :

CREATE OR REPLACE PROCEDURE INS_EMP (
  p_employee_id      IN  employees.employee_id%TYPE,
  p_first_name       IN  employees.first_name%TYPE,
  p_last_name        IN  employees.last_name%TYPE,
  p_email            IN  employees.email%TYPE,
  p_phone_number     IN  employees.phone_number%TYPE,
  p_hire_date        IN  VARCHAR2,
  p_job_id           IN  employees.job_id%TYPE,
  p_salary           IN  employees.salary%TYPE,
  p_commission_pct   IN  employees.commission_pct%TYPE,
  p_manager_id       IN  employees.manager_id%TYPE,
  p_department_id    IN  employees.department_id%TYPE
)
AS
BEGIN
  INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, HIRE_DATE, job_id, salary, commission_pct, manager_id, department_id)
  VALUES (p_employee_id, p_first_name, p_last_name, p_email, p_phone_number, TO_DATE(p_hire_date, 'YYYY-MM-DD'), p_job_id, p_salary, p_commission_pct, p_manager_id, p_department_id);
EXCEPTION
  WHEN OTHERS THEN
    HTP.print(SQLERRM);
END;
/

The next step is to create the web service with a POST handler calling the stored procedure, passing the the parameters needed by the PL/SQL procedure :

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v4',
    p_base_path      => 'rest-v4/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'rest-v4',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v4',
    p_pattern        => 'employees/',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           INS_EMP(p_employee_id      =>  :employee_id,
                                           p_first_name       =>  :first_name,
                                           p_last_name        =>  :last_name,
                                           p_email            =>  :email,
                                           p_phone_number     =>  :phone_number,
                                           p_hire_date        =>  :hire_date,
                                           p_job_id           =>  :job_id,
                                           p_salary           =>  :salary,
                                           p_commission_pct   =>  :commission_pct,
                                           p_manager_id       =>  :manager_id,
                                           p_department_id    =>  :department_id);
                         END;',
    p_items_per_page => 0);

  COMMIT;
END;

We have to use a Rest Client to pass the parameters expected by the POST method (Advanced Rest Client extension for Chrome or  RestClient plugin for Firefox).

The web service now can be called using :

• The URL :  http://192.168.1.49:8080/ords/hr/rest-v4/employees/
• Method : POST
• Headers : Content-Type : application/json
• Body  – remember the web server send JSON format : {“employee_id”:9998,”first_name”:”LAZHAR”,”last_name”:”FELAHI”,”email”:”LAZOO”,”phone_number”:”777″,”hire_date”:”2020-01-01″,”job_id”:”IT_PROG”,”salary”:5000,”commission_pct”:null,”manager_id”:201,”department_id”:10}

The goal of this POST method is to create the new employee_id “9998” into the table EMPLOYEES.

Let’s check if the employee exist before to execute the POST method :

select * from employees where employee_id = '9998';
no rows selected

Let’s execute the POST method by clicking on the SEND Button from the REST Client :

Check the POST method execution is successful (Green Button with message 200 OK).

Let’s check into the database if the new employee is created :

select * from employees where employee_id = '9998';

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- -------- ---------- ---------- -------------- ---------- -------------
       9998 LAZHAR               FELAHI                    LAZOO                     777                  01.01.20 IT_PROG          5000                       201            10

Conclusion :

• A lot of features exists with ORDS RESTful Web Services. In this blog, we just use standard HTTP calls via GET (Select/Read) and POST (Create/Insert) method interacting with the database.
• More complex RESTfull API can be written : Web Service with multiple value parameters, uploads files as  BLOBS, create API interacting with your mobile phone.