Creating and Using RESTful Web Service

Oracle database gives an option to access its table using REST interfaces. This blog walks you thru the steps how one could configure it.

Before you configure the REST create a new schema. If you already have an schema, you can proceed with step #2

1. Create New Schema

[oracle@PersonifyDB opc]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 13 01:22:23 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> alter session set container = PDB1;

Session altered.

SQL> create user demo identified by password;

User created.

SQL> grant connect, resource to demo;

Grant succeeded.

SQL> grant unlimited tablespace to demo;

Grant succeeded.

sqlplus demo/password@//PersonifyDB.compute-596091264.oraclecloud.internal/pdb1.596091264.oraclecloud.internal
sqlplus demo/password@//host/fullyqualifiedpdbname


CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Table Created;

2.  In next few steps you would be creating an new workspace using apex

a. Log on to Apex

b. Create on "Create Workspace" from "Managed Workspace tab.

c.  Give the require details as per your project

d. Select the schema that you created in step#1. If you want to use existing schema for creating your REST project, search and select the schema in this step. Click Next. This will show you the summary of Workspace that is getting created.

e. Check REST Configuration

3. Logout of the admin work space and login to your new work space. Use the work space details completed in step#2

a. Select SQL Workshop

b. Select Restful Services

4. Create Restful Project or configuration. In this step, we would be creating an REST project and exposing 2 methods. The GET method would be used to fetch all the records from the table that we had created earlier. The post method will be used to create a new record in the table that we had created earlier.

a. Click on "Create a new RestFul Project". If you select "Install a sample restful", it would automatically create the rest project for the default HR schema.

b. Give a name to your project. select and URI Prefix. This prefix will be part of the base url. Make sure you end it with '/'. The below image does not have '/' appended. Think this as the base project name

c. Next give details about "Resource template".  Think this as the table name. Again end the URI template with /. For example 'customer/'

d. For this, we would be create 2 resource handlers. One for fetch and one for save. For Get select method as 'GET', format as JSON, Source type as Query and source as "Select * from customers" and click Save

e. Similarly create one for 'Post'. For this, click on 'Create Handler' from the left navigation

f. Select method as 'Post', Mime type as 'JSON' also select Require secure access as No 

Write a simple plsql that inserts record into the table.

begin

    insert into customers values (:id, :name, :city);

    :status := 200;

exception

    when others then

        :status := 400;

end;

Make a note of the pl sql variable. These variables will translate to as JSON schema parameters.

g. Click Pubish

5. Access REST using URL

6. Use post for sample post

The rest url generated: https://ip/ords/portabledb/workspace/baseuri/resourceuri

As your can see, just by going thru few steps, you can configure database access using REST.