giovedì 12 maggio 2016

Oracle Rest Data Service & Oracle Cloud (First Part): how to expose your data with REST in a very simple and fast way

In this post I'll present ORDS (Oracle Rest Data Services) we will work on Oracle Cloud, but if you are interested in It you can download ORDS and install It on premise, you can find HERE all the informations.

In Oracle Database Cloud Service, ORDS is already installed and running and it is very simple to create a Rest Data Service using as starting point a table tha already exist in the Database.

In this example I'll use the EMPLOYEE table under the HR user:

(some images captured from my SQL Developer connected to my Oracle Database Cloud Service )

I'll cretae a REST service that is able to receive in the URL the employee_id, execute the query "select * from employees where employee_id = : employee_id" and that return a JSON object.

To be able to create the Rest Data Service we will use the APEX (Application Express) console from the Oracle Database Cloud Service, this console respond in HTTPS so we need to open that protocol and port from the Oracle Cloud Compute Node:

Connect to your Oracle Database Cloud Service and select Oracle Compute Cloud Service:

Select Network and enable https for the DB service:

Now return in your Oracle DBCS and open the APEX console:

The first time you need to use the following information to connect as Administartor:
your ADMIN password (the one that you use also in SQL Developer to connect)

As ADMIN you can create now a workspace where you can create all the Rest Data Services that you need, so click on "Create Workspace":

Choose a name for your workspace (this will appear in the Rest URL).
Click the Next Button:

I'll use an existing schema and table , so I have selected YES on the field "Re-use existing schema?", then click on the field "Schema Name" to choose which table expose via REST:

a popup will appear with the existing tables names, choose your one:

Click the Next button:

Choose an Admin username & password.
Click on the Next button:

Review all the inserted data then click on the "Create Workspace" button:

Your workspace is created, click on "Done" button:

Logout from the console:

Now you can login in your new workspace with your new credentials:

Use the name of your new workspace, the new ADMIN and PASSWORD:

After the first login in the new workspace you must change the password:

Choose a new password then click on "Apply Changes":

Now select "SQL Workshop", here we can create REST Data Services:

Select "RESTful Services":

Click on the "Create" button:

Choose a Name for your REST Data Service, this will appear in the console, Choose an URI Prefix, this will appear in the REST URL:

Name: hr
URI Prefix: hr/

Choose an URI Template, this will appear in the REST URL, if you whant to pass parameter use {}

URI Template: employees/{employee_id}

Example, the REST URL:
will return a JSON with the resul of the query:
"select * from employees where employee_id = : employee_id

Scroll down the page:

GET as Method
Source Type=Query

Insert you Query in the Source field:
select * from employees where employee_id = : employee_id

Click on Create Module:

You have created your REST Data Service.
To test It click on the GET link on the left panel:

Scroll down the page and click on the "Set Bind Variables" button:

Put 101 in the EMPLOYEE_ID field and then click on the Test button:

You will receive a popup with the JSON response:

I have installed the "JSONView" plugin for Chrome to have a readable answer:

This is the JSONView plugin.

As soon as possible I'll write some example of code on how to call those kind of Rest data Services from Java and Node.js.

1 commento: