venerdì 13 maggio 2016

Oracle Rest Data Service & Oracle Cloud (Second Part): How to INSERT (POST) data with REST

In the [FIRST] part you have learned how to create a REST Data Service in Oracle Database (in Cloud)

We have create a SELECT (GET), now we will see how to create an INSERT (POST).

You can use the "auto rest" (more info here) but with this you can do only simple insert, with primary key and only a "select * ..." if you would like to specialized your INSERT (or UPDATE or DELETE) you must do what I will explain in this post.

We will create a Data Service that will insert a row in the "emp" table of the HR user:


This is the configuration:
Insert in the Source field your preferred insert:

begin  
    insert into emp (empno,ename,job,hiredate,sal,deptno) values   
    (:empno,:ename,:job,:hiredate,:sal,:deptno);  
 end; 

This is an example, you can write every kind of complex insert.


 Now you can test it, I suggest to use something like "Postman":

use your url in the POST field:
https://yourIP/ords/pdb1/mariniws/hr/emp/

Set in the Headers tab:
Content-Type: application/json



Select the Body tab and put here the data to send:

 {  
  "empno":1234,  
  "ename":"Marini",  
  "job":"PRESIDENT",  
  "hiredate":"2003-02-01T08:00:00Z",  
  "sal":5000,  
  "deptno":10  
 }  

Click the "Send" button:
If all It is ok, you will receive a Status 200 OK


If you refresh SQL Developer you can find the new row:




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:
INTERNAL
ADMIN
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:
https://myIP/ords/pdb1/mariniws/hr/employees/101
will return a JSON with the resul of the query:
"select * from employees where employee_id = : employee_id

Scroll down the page:


Select:
GET as Method
Source Type=Query
Format=JSON

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.



lunedì 9 maggio 2016

Meetup DevOps - Rome, 31 May 2016 - Oracle, Via Bombay 1 - Eur (Torrino) - 5:00 PM to 7:15 PM

 MeetUp DevOps

In you are interested on DevOps & Cloud, this MeetUp is for you, no slide and a lot of hands-on :-)

We will see:
  • Oracle Developer Cloud Service
  • Oracle Java Cloud Service
  • Oracle Application Container Cloud Service
    • JavaSE Container
    • Node.js Container

For more information click [HERE]

Meetup DevOps
Rome, 31 May 2016
Oracle, Via Bombay 1 - Eur (Torrino)
5:00 PM to 7:15 PM