Create RESTful services with Oracle DBCS

In this post I will provide an overview of how to expose the data from Oracle DBCS via RESTful services. This is a common scenario where the customer would like to store large volumes of data in DBCS and leverage other Oracle PaaS products to build custom applications (ex : VBCS comes with a limited internal storage capacity to overcome this limitation we can leverage DBCS and expose the data via RESTful services)

I have created a schema and added the EMP table for the table as shown below.

EMP

===

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 the EMP table with some sample data.

Now navigate to APEX and select SQL Workshop > RESTful Services

Create a new module by providing the relevant details.

After the module is created click on the Create Template button at the bottom of the page.

Enter the details as shown below and create the template

After the template has been created, click on Create Handler.

Under Handler section specify the method, source type, format and source options and click on create handler.

Now copy the Full URL Path and navigate to a browser and place the copied path.

The GET method will query the underlying EMP Table exposes the data in JSON format.
Similarly, we can created handlers for other methods as well.

By default authentication is not enabled in ORDS. Do refer the Authentication section for the options available.