Build rest web service integration system in ORDS

Skip to Main Content

Hi Everyone

I have been recently working on a project where one of the tasks was to develop an integration layer in Oracle Rest Data Service. So, I thought that it would be interesting to share that experience in this blog post. The main task was to develop an integration interface with the help of ORDS where multiple platforms can send and fetch data from the Oracle database. For example building rest webservices architecture between Oracle database and Microsoft rest based platforms.
There are number of ways to implement this but I will try to demonstrate one way of doing it. I have build similar architectures in the past using different framework's but I think that ORDS has great advantage over other frameworks when it comes to code implementation, Integration, configuration and a large scale data management.

To demonstrate this blog post I have used Oracle sql developer, PL/SQL and Oracle rest data service (ORDS). You can find code for this blog on GitHub

To keep the architecture simple I have divided it into three layers. See the image below

A. Data layer. It can be a single database instance or multiple databases
B. Business logic layer for code implementation
C. Integration layer & Rest Webservice. To process rest requests GET/POST/PUT/DELETE and logs it accordingly.

Lets start with the Data layer. I have created two tables DEPT, EMP in the database.

In the logic layer I want to get the employee information based on the department number. So, I have created a logic as a package and added get_rows_by deptno as a function in it. You can also think to have a separate database schema for the logic implementation.
See the code below

create or replace package
logic
as


type
dept_rec
is record
(
         empno emp.empno%
type

        ,ename emp.ename%
type

        ,hiredate emp.hiredate%
type

        ,dname dept.dname%
type

        ,job emp.job%
type

        ,salary emp.salary%
type

        ,location dept.location%
type

    );


type
dept_rec_tbl
is table of
dept_rec;


function
get_rows_by_deptno (p_in_deptno
in
dept.deptno%
type
)
return
dept_rec_tbl;

end
logic;

--Let's add the package body. In the package body section I have added one function get_rows_by_deptno and It is taking in one parameter p_in_deptno and returning employee info based on the department
--number.

create or replace package body
logic
as

function
get_rows_by_deptno (p_in_deptno
in
dept.deptno%
type
)
return
dept_rec_tbl

is

  lv_rows_by_dname dept_rec_tbl;


begin


select

      empno,
      ename,
      hiredate,
      dname,
      job,
      salary,
      location

bulk collect into
lv_rows_by_dname

from
emp, dept

where
emp.deptno = dept.deptno

and
dept.deptno = p_in_deptno

order by
hiredate;


return
lv_rows_by_dname;

end
get_rows_by_deptno;

end
logic;

Now we will start implement integration layer before I will create REST api in the ORDS. I will first create a integration as a package and then add a function which will call logic.get_rows_by_deptno and fetch the data from logic layer to the integration layer. You can also think to have integration as a separate database schema so that you can make separation between logic and rest functionality. In my case I will create integration package. see the code below.

create or replace type
t_dept_obj
as object

    (
    empno
number
,
    ename
varchar2(100)
,
    hiredate
date
,
    dname
varchar2(100)
,
    job
varchar2(100)
,
    salary
number
,
    location
varchar2(100)

    );

create or replace type
t_dept_tblo
is table of
t_dept_obj;

create or replace package
integration
as


function
get_rows_by_deptno_piped(p_in_deptno
in
dept.deptno%
type
)
return
t_dept_tblo
pipelined
;

end
integration;

--In the package body I will add a function integration.get_rows_by_deptno_piped and call logic.get_rows_by_deptno. I will also add few local variables like lv_api_id = 3 and lv_api_type = GET and lv_payload
--because when the webservice endpoints will be called I will log it into the rest request log table. I want to see that which department number was sent in the request. See the below code for
--integration.get_rows_by_deptno_piped

create or replace package body
integration
as

function
get_rows_by_deptno_piped(p_in_deptno
in
dept.deptno%type)
return
t_dept_tblo
pipelined

is

   get_rows_deptno logic.dept_rec_tbl;
  lv_api_id
number
:= 3;
--assigned api id = 3

  lv_api_type
varchar2
(10):=
'GET'
-- api type = GET
;
  lv_payload
clob
;

begin

    get_rows_deptno:=logic.dept_rec_tbl();
    get_rows_deptno:=logic.get_rows_by_deptno(p_in_deptno);


for
indx
in
1 .. get_rows_deptno.
count


loop


pipe row
(t_dept_obj(
        get_rows_deptno(indx).empno
       ,get_rows_deptno(indx).ename
       ,get_rows_deptno(indx).hiredate
       ,get_rows_deptno(indx).dname
       ,get_rows_deptno(indx).job
       ,get_rows_deptno(indx).salary
       ,get_rows_deptno(indx).location
       ));

end loop
;

-- build json payload to log the requested department no

   apex_json.initialize_clob_output();
   apex_json.open_object();
   apex_json.
write
('department_no : ', p_in_deptno);
   lv_payload := apex_json.get_clob_output();
   apex_json.free_output();
   log_message (lv_api_id, lv_payload);
-- see simple logging function

end
get_rows_by_deptno_piped;

end
integration;

Now I will create rest webservice in sql developer. I assume that you already know how to create ORDS GET in sql developer. You can also see my previous blog posts for more information on it.

I create a service findbydeptno and it takes one parameter deptno in the uri. You see below that the select statement.
Once the endpoints will be called it will execute the above code and logs this request.

select
*
from table
(integration.get_rows_by_deptno(:deptno))

Output from postman

I have logged the postman request as a payload in the log table.

I have also created a chart and a report in Oracle Application Express to see the analytical view of system rest webservices.

web counter

Name

Comments

Rating