Introduction

In the first part of the article we will provide an overview of how you can use Oracle REST Data Services for providing APIs directly from your PL/SQL code . The second part covers how to document our Web services using Swagger. Lets begin with a couple of technical concepts:

• ORDS: Oracle REST Data Services ORDS enables developers with SQL and database skills to develop REST APIs for the Oracle Database. In a few words ORDS is:
  • A mid-tier Java application.
  • Runs in a Java application server like WebLogic or Tomcat.
  • Maps standard http(s) RESTful requests to database transactions.
    • Access to Relational data over HTTP(s) without installing JDBC/ODBC drivers
  • Can declaratively returns results in JSON format.
  • Can connect to Oracle NoSQL and Oracle container databases in Cloud.
  • Supports Swagger based Open API integration.
  • It was formally known as Oracle APEX Listener.

Figure 1: Relational to JSON with ORDS

The picture above shows the ORDS architecture and how does it work. It basically acts as a middleman between clients (applications) and the database, mapping incoming HTTP(S) requests to resource handlers for a specific URI pattern. Resource handlers can have different source types, such as query and PL/SQL. With the query source type, ORDS executes the query, converts the results into JSON, and returns the JSON to the client.

• ORDS - URL Structure. The URL for an ORDS Web Service consists of five elements: https://<host>:<port>/ords/<schema>/<module>/<template>
  • Host: Name of the host.
  • Port: Port Number.
  • Schema: Pattern defined for the schema.
  • Module: Base path for the module.
  • Template: Pattern defined for the template.
• Swagger: Swagger is an open-source software framework supported by a large tool ecosystem that helps developers design, build, document, and consume RESTful web services. While most users use the Swagger UI tool to identify Swagger, the Swagger tool set includes automated documentation support, code generation and test case generation.

It is out of the scope how to install and configure ORDS. For that purpose you can check this fantastic article from Tim Hall: https://oracle-base.com/articles/linux/docker-oracle-rest-data-services…

REST services, how to.

This example will show us how to create a Web Services within a new schema called ORDSEXAMPLE.

Create a new user

Log as SYSDBA and create a new user with the necessary privileges using the following SQL:

ALTER SESSION SET CONTAINER =pdb1;
CREATE USER ORDSEXAMPLE IDENTIFIED BY ordsexample_1995 DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
GRANT CREATE TABLE TO ORDSEXAMPLE;
GRANT CREATE SESSION TO ORDSEXAMPLE

Prepare your schema

Create a copy of the good old classic Oracle EMP and DEPT tables with sample data using the following SQL snippets:

CREATE TABLE dept (
    deptno   NUMBER(2, 0),
    dname    VARCHAR2(14),
    loc      VARCHAR2(13),
    CONSTRAINT pk_dept PRIMARY KEY ( deptno )
);

CREATE TABLE emp (
    empno      NUMBER(4, 0),
    ename      VARCHAR2(10),
    job        VARCHAR2(9),
    mgr        NUMBER(4, 0),
    hiredate   DATE,
    sal        NUMBER(7, 2),
    comm       NUMBER(7, 2),
    deptno     NUMBER(2, 0),
    CONSTRAINT pk_emp PRIMARY KEY ( empno ),
    CONSTRAINT fk_deptno FOREIGN KEY ( deptno )
        REFERENCES dept ( deptno )
);

Populate them:

INSERT INTO DEPT VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES(20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES(40,'OPERATIONS','BOSTON');
INSERT INTO DEPT VALUES(50,'PURCHASING','WASHINGTON');
INSERT INTO DEPT VALUES(60,'HR','NEW YORK');

INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('19-APR-87','DD-MON-RR'),3000,NULL,20);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('01-MAY-81','DD-MON-RR'),2850,NULL,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('17-NOV-81','DD-MON-RR'),5000,NULL,10);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7566,'JONES','MANAGER',7839,TO_DATE('02-APR-81','DD-MON-RR'),2975,NULL,20);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('09-JUN-81','DD-MON-RR'),2450,NULL,10);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7902,'FORD','ANALYST',7566,TO_DATE('03-DEC-81','DD-MON-RR'),3000,NULL,20);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('23-MAY-87','DD-MON-RR'),1100,NULL,20);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('20-FEB-81','DD-MON-RR'),1600,300,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7900,'JAMES','CLERK',7698,TO_DATE('03-DEC-81','DD-MON-RR'),950,NULL,50);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('28-SEP-81','DD-MON-RR'),1250,1400,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7934,'MILLER','CLERK',7782,TO_DATE('23-JAN-82','DD-MON-RR'),1300,NULL,10);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-DEC-80','DD-MON-RR'),800,NULL,60);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('08-SEP-81','DD-MON-RR'),1500,0,30);
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('22-FEB-81','DD-MON-RR'),1250,500,40);
COMMIT;

We can use check that the tables are correctly filled and include the default collection of six divisions and fourteen employees:

SELECT
    ename,
    dname,
    job,
    empno,
    hiredate,
    loc
FROM
    emp,
    dept
WHERE
    emp.deptno = dept.deptno
ORDER BY
    emp.ename;

Enable the ORDS schema

Before creating any Web Services, we have to enable REST data services for the ORDSEXAMPLE schema:

BEGIN
    ords.enable_schema(p_enabled => TRUE,
                                    p_schema => 'ORDSEXAMPLE',
                                    p_url_mapping_type => 'BASE_PATH',
                                    p_url_mapping_pattern => 'api',
                                    p_auto_rest_auth => FALSE);
    COMMIT;
END;

Web services from the schema can now be referenced using the following base URL: http://localhost:8080/ords/ORDSEXAMPLE/

Define Module

We name the resource module hr.v1. A Web Service name should always include the version number; this will allow us to publish updated versions of a Web Service that follow the same URL patterns:

BEGIN
    ords.define_module(p_module_name => 'hr.v1',
                                   p_base_path => 'hr/v1/',
                                   p_items_per_page => 10,
                                   p_status => 'PUBLISHED',
                                   p_comments => 'Sample HR Module');
    COMMIT;
END;

Define Template

BEGIN
    ords.define_template(p_module_name => 'hr.v1',
                                     p_pattern => 'departments',
                                     p_comments => 'Departments Resource');
                                     ords.define_template(p_module_name => 'hr.v1',
                                     p_pattern => 'employees',
                                     p_comments => 'Employees Resource');
    COMMIT;
END;

Define the handlers

One for each HTTP method: GET, POST,PUT and DELETE

-- The GET Handler method on the departments template
BEGIN
  ords.define_handler(p_module_name    => 'hr.v1',
                      p_pattern        => 'departments',
                      p_method         => 'GET',
                      p_source_type    => ords.source_type_query,
                      p_source         => 'SELECT deptno, dname, loc
                                           FROM dept ORDER BY deptno',
                      p_items_per_page => 5,
                      p_comments       => 'List departments');
  COMMIT;
END;

-- The POST Handler method on the departments template
BEGIN
    ords.define_handler(p_module_name => 'hr.v1', p_pattern => 'departments', p_method => 'POST', p_source_type => ords.source_type_plsql
    , p_source => 'BEGIN
                                          INSERT INTO dept
                                            (deptno, dname, loc)
                                          VALUES
                                            (:pn_dept_no, :pv_dept_name, :pv_location);
                                          :pn_status := 200;
                                          :pv_result := ''Department Added'';
                                        EXCEPTION
                                          WHEN OTHERS THEN
                                            :pn_status := 400;
                                            :pv_result := ''Unable to add department: ''
                                                          || SQLERRM;
                                        END;'
    , p_comments => 'Create a Department');
END;

-- The PUT Handler method on the departments template

BEGIN
    ords.define_handler(p_module_name => 'hr.v1', p_pattern => 'departments', p_method => 'PUT', p_source_type => ords.source_type_plsql
    , p_source => 'BEGIN
                                          UPDATE dept
                                          SET dname = :pv_dept_name, loc = :pv_location
                                          WHERE deptno = :pn_dept_no;
                                          IF SQL%ROWCOUNT = 0 THEN
                                            :pn_status := 400;
                                            :pv_result := ''Invalid department number'';
                                          ELSE
                                            :pn_status := 200;
                                            :pv_result := ''Department Updated'';
                                          END IF;
                                        EXCEPTION
                                          WHEN OTHERS THEN
                                            :pn_status := 400;
                                            :pv_result := ''Unable to update department:''
                                                          || SQLERRM;
                                        END;'
    , p_comments => 'Create a Department');
END;

-- The DELETE Handler method on the departments template
BEGIN
    ords.define_handler(p_module_name => 'hr.v1', p_pattern => 'departments', p_method => 'DELETE', p_source_type => ords.source_type_plsql
    , p_source => 'BEGIN
                                          DELETE FROM dept
                                          WHERE deptno = :pn_dept_no;
                                          IF SQL%ROWCOUNT = 0 THEN
                                            :pn_status := 400;
                                            :pv_result := ''Invalid department number'';
                                          ELSE
                                            :pn_status := 200;
                                            :pv_result := ''Department Deleted'';
                                          END IF;
                                        EXCEPTION
                                          WHEN OTHERS THEN
                                            :pn_status := 400;
                                            :pv_result := ''Unable to delete department: ''
                                                          || SQLERRM;  
                                        END;'
    , p_comments => 'Delete a Department');

    COMMIT;
END

-- The GET Handler method on the employees template
BEGIN
    ords.define_handler(p_module_name => 'hr.v1', p_pattern => 'employees', p_method => 'GET', p_source_type => ords.source_type_query
    , p_source => 'SELECT d.dname, e.ename, e.job,
                                               e.empno, e.hiredate, d.loc
                                        FROM emp e, dept d
                                        WHERE e.deptno = d.deptno
                                        AND (:pn_deptno IS NULL OR d.deptno = :pn_deptno)
                                        ORDER BY d.dname, e.ename'
    , p_comments => 'List employees');

    COMMIT;
END;

Define Parameters

The following scripts will define all parameters for each handler:

--Departments PUT

BEGIN
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'PUT',
                        p_name               => 'department_number',
                        p_bind_variable_name => 'pn_dept_no',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'IN',
                        p_comments           => 'Department Number');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'PUT',
                        p_name               => 'department_name',
                        p_bind_variable_name => 'pv_dept_name',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'STRING',
                        p_access_method      => 'IN',
                        p_comments           => 'Department Name');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'PUT',
                        p_name               => 'location_name',
                        p_bind_variable_name => 'pv_location',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'STRING',
                        p_access_method      => 'IN',
                        p_comments           => 'Location Name');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'PUT',
                        p_name               => 'X-APEX-STATUS-CODE',
                        p_bind_variable_name => 'pn_status',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'OUT',
                        p_comments           => 'Response status');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'PUT',
                        p_name               => 'result_message',
                        p_bind_variable_name => 'pv_result',
                        p_source_type        => 'RESPONSE',
                        p_param_type         => 'STRING',
                        p_access_method      => 'OUT',
                        p_comments           => 'Result message');
  COMMIT;
END;

--Department DELETE

BEGIN
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'DELETE',
                        p_name               => 'department_number',
                        p_bind_variable_name => 'pn_dept_no',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'IN',
                        p_comments           => 'Department Number');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'DELETE',
                        p_name               => 'X-APEX-STATUS-CODE',
                        p_bind_variable_name => 'pn_status',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'OUT',
                        p_comments           => 'Response status');
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'departments',
                        p_method             => 'DELETE',
                        p_name               => 'result_message',
                        p_bind_variable_name => 'pv_result',
                        p_source_type        => 'RESPONSE',
                        p_param_type         => 'STRING',
                        p_access_method      => 'OUT',
                        p_comments           => 'Result message');
  COMMIT;
END;

--Employee GET

BEGIN
  ords.define_parameter(p_module_name        => 'hr.v1',
                        p_pattern            => 'employees',
                        p_method             => 'GET',
                        p_name               => 'department_number',
                        p_bind_variable_name => 'pn_deptno',
                        p_source_type        => 'URI',
                        p_param_type         => 'INT',
                        p_access_method      => 'IN',
                        p_comments           => 'Restrict employees by department');  
  COMMIT;
END;

We can use the ORDS_METADATA views to query them. The following SQL query will return all of the Web Services defined for the ORDSEXAMPLE schema.

--Reviewing the Web Services
SELECT
    uom.comments   module_desc,
    uot.comments   template_desc,
    uoh.comments   handler_desc,
    uoh.method,
    uoh.source_type,
    '<host_ref>'
    || uos.pattern
    || uom.uri_prefix
    || uot.uri_template url,
    (
        SELECT
            COUNT(id)
        FROM
            user_ords_parameters
        WHERE
            handler_id = uoh.id
    ) parameter_count
FROM
    user_ords_schemas     uos,
    user_ords_modules     uom,
    user_ords_templates   uot,
    user_ords_handlers    uoh
WHERE
    uot.module_id = uom.id
    AND uom.schema_id = uos.id
    AND uoh.template_id = uot.id
    AND uos.parsing_schema = 'ORDSEXAMPLE'
ORDER BY
    uom.comments,
    uot.uri_template
--Example  <host_ref> = http://localhost:8080/ords/

Testing Web Services

Just choose your preferred HTTP client!

Generating a Swagger document through ORDS

1- Using the open-api-catalog (Swagger Editor)

The open-api-catalog output is in Open API (Swagger) 2.0 format, which makes it really simple to generate documentation and an example of the calling code in many programming languages.

An overview of the contents of an ORDS enabled schema can be displayed in a browser (GET HTTP method) using the following type of URL. The output is a JSON document:

• Format: http://server:port/ords/<connection>/<schema-alias>/open-api-catalog/<o…;
• Example: http://localhost:8080/ords/api/open-api-catalog/hr/v1/

Go to the online Swagger Editor (https://editor.swagger.io/), paste the output JSON text. It will be converted to YAML and display the available endpoints.

2- Using the swagger-UI server

In the previous version of APEX was not obvious to generate this documentation. The new setting takes a URL which points to a SWAGGER UI 2.0 server. If the instance setting is set, the URL that generates the web service’s swagger JSON document will be sent to the Swagger UI server. If there is no URL specified, raw JSON will be produced.

Figure 2: Setting the Swagger URL

Setting up the Swagger server is actually quite easy. It is a simple set of HTML, Javascript and CSS files that can be downloaded from here (https://swagger.io/tools/swagger-ui/) and unzipped into a directory into the web root of our local web server. During my test I pulled a pre-built docker image of the swagger-ui directly from Docker Hub. and I ran it in a separate container http://localhost:8888/. Then, from ORDS REST Workshop, navigate to the module definition level and click the Generate Swagger Doc button.

If the Swagger UI URL is set correctly at the Instance level, APEX will forward the URL of the documentation to the Swagger UI Server. The server must be able to reach back to the documentation URL, but as long as it can, we will not only get documentation but be able to test the services as well.

Figure 3: APEX REST Workshop for generating swagger documents

Figure 4: Documentation generated by Swagger UI

Acknowledgements and Reference links

My greatest thanks go to my supervisor Luis Rodriguez Fernandez who has been guiding me throughout the program. I think him for all the time devoted to me, for his total availability, for his kindness and especially for his remarks and constructive criticism. I am also thankful to all the IT-DB group members for their warm welcome, their help during this project and for making this experience very pleasant and rewarding.

• https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19…
• https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-op…