- Renaming a RAC cluster - 27/09/2018
- Stop/Start all RAC databases at once - 26/09/2018
- RMAN Backup script - 08/11/2017
Presentation
First, let’s talk about REST apis. REST apis are very popular nowdays because they are easily accessible from any client able to make http(s) requests. The beauty of this is that you can access different systems through these rest apis independantly of how the system is built in the backend. For example I can access with a simple curl command the twitter, youtube or any public api which will return me data.
Rest APIs usually present data as a JSON object. If your are not already familiar with JSON here is an example of a JSON object:
{"menu": {
"id": "file",
"value": "File",
"popup": {
"menuitem": [
{"value": "New", "onclick": "CreateNewDoc()"},
{"value": "Open", "onclick": "OpenDoc()"},
{"value": "Close", "onclick": "CloseDoc()"}
]
}
}}
Oracle 12.1.0.2 introduced the support of JSON as a datatype and methods to query JSON objects. This make things a lot easier when you have to work with these data structures.
What the point of using a REST API for a database?
Activating a REST service for your database gives you the possibility to access and modify your data directly through a simple http(s) request which is an easy way to give access to your data for an existing application.
Using REST also alow to create a clear separation between database development and software development. Most of the time, when a software developer wants to use a Database through an application he will have to learn SQL and know the particularity of database specific functions, views, and procedural language. He also have to use specific drivers to access the database and learn how they work. Using REST, the software developer only have to call a web service when he wants to interact with the database which simplify his life, leaving people with database knowledge develop the service they will call.
ORDS is free of charge and can be deployed on WebLogic, Tomcat and Glassfish application servers or as a standalone service.
Installing and configuring Oracle Rest Data Services
Install
First you will need to download ORDS from the following link:
Download
In this post I will deploy ORDS as a standalone service running directly on the database server. Like explained earlier, once configured ORDS can also be deployed on a compatible application server.
Upload the zip file to your server and unzip it to a temprary directory:
# unzip ords.3.0.11.180.12.34.zip -d /tmp/ords
Configure
Start the configuration using:
# java -jar ords.war
Here I chose not to use APEX and run the ORDS as a standalone service. To keep things simple I also used HTTP over HTTPS:
[oracle@srv-ora-01 ords]$ java -jar ords.war
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts
Enter the location to store configuration data:/u01/app/oracle/product/ords
Enter the name of the database server [localhost]:192.168.1.50
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:orclpdb
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema.
Enter the username with SYSDBA privileges to verify the installation [SYS]:
Enter the database password for SYS:
Confirm password:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Nov 05, 2017 10:41:23 PM
INFO: Updated configurations: defaults, apex_pu
Installing Oracle REST Data Services version 3.0.11.180.12.34
... Log file written to /tmp/ords/logs/ords_install_core_2017-11-05_224123_00591.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /tmp/ords/logs/ords_install_datamodel_2017-11-05_224138_00610.log
Completed installation for Oracle REST Data Services version 3.0.11.180.12.34. Elapsed time: 00:00:15.887
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:
Enter 1 if using HTTP or 2 if using HTTPS [1]:
2017-11-05 22:42:07.401:INFO::main: Logging initialized @359829ms
Nov 05, 2017 10:42:07 PM
INFO: Disabling document root because the specified folder does not exist: /u01/app/oracle/product/ords/ords/standalone/doc_root
2017-11-05 22:42:08.232:INFO:oejs.Server:main: jetty-9.2.z-SNAPSHOT
Nov 05, 2017 10:42:08 PM
INFO: No encryption key found in configuration, generating key
Nov 05, 2017 10:42:08 PM
INFO: No mac key found in configuration, generating key
Nov 05, 2017 10:42:08 PM
INFO: Updated configurations: defaults
Nov 05, 2017 10:42:08 PM
INFO: Updated configuration with generated keys
2017-11-05 22:42:08.446:INFO:/ords:main: INFO: Using configuration folder: /u01/app/oracle/product/ords/ords
2017-11-05 22:42:08.448:INFO:/ords:main: FINEST: |ApplicationContext [configurationFolder=/u01/app/oracle/product/ords/ords, services=Application Scope]|
Nov 05, 2017 10:42:08 PM
INFO: Validating pool: |apex|pu|
Nov 05, 2017 10:42:08 PM
INFO: Pool: |apex|pu| is correctly configured
config.dir
2017-11-05 22:42:08.730:INFO:/ords:main: INFO: Oracle REST Data Services initialized|Oracle REST Data Services version : 3.0.11.180.12.34|Oracle REST Data Services server info: jetty/9.2.z-SNAPSHOT|
2017-11-05 22:42:08.732:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@289a0cdb{/ords,null,AVAILABLE}
2017-11-05 22:42:08.739:INFO:oejs.ServerConnector:main: Started ServerConnector@5cca1a7f{HTTP/1.1}{0.0.0.0:8080}
2017-11-05 22:42:08.740:INFO:oejs.Server:main: Started @361168ms
Now the service is started and you should be able to access it through a web browser. If you access to http://<ip_or_host_configured>:8080/ords.
You should have the following page:
The 404 is completly normal.
Getting Started
Now that we have configured ORDS, why not enable some services to access our data?
For my tests I will use the Oracle sample schema HR. The first thing we need to do is enable Restful services for the schema, this is done through the ORDS package:
SQL> DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
p_schema => 'HR',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'hr',
p_auto_rest_auth => TRUE);
commit;
END;
/
PL/SQL procedure successfully completed.
The url_mapping_pattern for the BASE_PATH describe the url structure necessary to access the services served by this schema. Now we are all set to create our first service.
Here I will create a service to query my EMPLOYEES table:
SQL> BEGIN
ORDS.define_service(
p_module_name => 'employees',
p_base_path => 'employees/',
p_pattern => 'GetAllEmployees/',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT * FROM EMPLOYEES',
p_items_per_page => 0);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
This service is now accessible through the following url : http://<ip_or_host_configured>:8080/ords/hr/employees/GetAllEmployees/
If you try to access this url through a web browser, you should obtain something like this:
We have created a simple web service to access our table, now let’s go a little bit further and try to add a filter. We define the following service:
BEGIN
ORDS.define_service(
p_module_name => 'employees',
p_base_path => 'employees/',
p_pattern => 'GetEmployee/:emp_no',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT * FROM EMPLOYEES WHERE employee_id=:emp_no',
p_items_per_page => 0);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
I will now try to access an employee based on the employee_id by using the following url: http://<ip_or_host_configured>:8080/ords/hr/employees/GetEmployee/102
And I get the following result:
We just saw how to interact with a specific row, here we selected a row but this could have been a delete statement as well. Now let’s be a little bit more ambitious, let’s say an application wants to send us data using our REST api, the data we will receive will be a JSON object containing the definition of the row(s) we will need to insert. For that purpose we will create a stored procedure to process the incoming data and insert it into the EMPLOYEES table. This time we will not use a GET but a POST message. By default ORDS treats the incoming data of a post as a blob object. In order to use the data we will need to convert it as clob. Here is the procedure:
create or replace PROCEDURE insert_employee(p_lob blob)
IS
-- Input
l_clob clob;
-- Input processing
l_dest_offsset integer := 1;
l_src_offsset integer := 1;
l_lang_context integer := dbms_lob.default_lang_ctx;
l_warning integer;
-- JSON processing
l_je JSON_ELEMENT_T;
l_jo JSON_OBJECT_T;
l_lo JSON_OBJECT_T;
l_ja JSON_ARRAY_T;
-- Table data
l_first_name VARCHAR2(20);
l_last_name VARCHAR2(25);
l_email VARCHAR2(25);
l_phone_num VARCHAR2(20);
l_hire_date varchar2(50);
l_job_id VARCHAR2(10);
l_salary NUMBER;
l_com NUMBER;
l_man_id NUMBER;
l_dpmt NUMBER;
BEGIN
-- transform the lob data into a clob object
dbms_lob.createTemporary(lob_loc => l_clob,cache => false);
dbms_lob.converttoclob(dest_lob => l_clob
,src_blob => p_lob
,amount => dbms_lob.lobmaxsize
,dest_offset => l_dest_offsset
,src_offset => l_src_offsset
,blob_csid => dbms_lob.default_csid
,lang_context => l_lang_context
,warning => l_warning);
-- create a JSON Object from our clob data
l_jo := JSON_OBJECT_T.parse(l_clob);
-- Get all items from our JSON
l_ja := l_jo.get_Array('items');
-- Loop into our JSON array
FOR i IN 0 .. l_ja.get_size - 1 LOOP
-- Get the current employee
l_lo := JSON_OBJECT_T(l_ja.get(i));
-- Extract column values from the JSON object
l_first_name := l_lo.get_String('first_name');
l_last_name := l_lo.get_String('last_name');
l_email := l_lo.get_String('email');
l_phone_num := l_lo.get_String('phone_number');
l_hire_date := to_date(l_lo.get_String('hire_date'),'YYYY-MM-DD"T"hh24:mi:ss');
l_job_id := l_lo.get_String('job_id');
l_salary := l_lo.get_Number('salary');
l_com := l_lo.get_Number('comission_pct');
l_man_id := l_lo.get_Number('manager_id');
l_dpmt := l_lo.get_Number('department_id');
-- Insert the row
INSERT INTO EMPLOYEES
VALUES (EMPLOYEES_SEQ.NEXTVAL,
l_first_name,
l_last_name,
l_email,
l_phone_num,
l_hire_date,
l_job_id,
l_salary,
l_com,
l_man_id,
l_dpmt);
END LOOP;
END insert_employee;
/
Now we need to create a service which will expose this procedure publicly. Take good attention to the type declared in the service:
BEGIN
ORDS.define_service(
p_module_name => 'employees',
p_base_path => 'employees/',
p_pattern => 'insert_employees/',
p_method => 'POST',
p_source_type => ORDS.source_type_plsql,
p_source => 'BEGIN
hr.insert_employee(:body);
END;',
p_mimes_allowed => 'application/json',
p_items_per_page => 0);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
Here we use ORDS.source_type_plsql because we call a PL/SQL procedure.
The :body variable is the default variable for data passed through a POST request to the REST service.
Let’s try to insert an employee, I will create a JSON file with the following content in /tmp:
{
"items": [{
"first_name": "Cyrille",
"last_name": "MODIANO",
"email": "example@example.com",
"phone_number": "515.123.123",
"hire_date": "2017-11-06T18:00:00",
"job_id": "AD_VP",
"salary": 1000,
"commission_pct": null,
"manager_id": 100,
"department_id": 90
}]
}
Let’s try to post ou data to the service. Here I will use curl with the following command:
[oracle@srv-ora-01 tmp]$ curl -i -X POST --data-binary @/tmp/employee.json -H "Content-Type: application/json" http://192.168.1.50:8080/ords/hr/employees/insert_employees/
and the output is:
HTTP/1.1 200 OK Transfer-Encoding: chunked
An http 200 means everything went well, here is the verification:
SQL> select * from employees where first_name='Cyrille';
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
221 Cyrille MODIANO example@example.com 515.123.123 06-NOV-17 AD_VP 1000 100 90
Here we just submited one employee, let’s try 3 at a time with the following JSON:
{
"items": [{
"first_name": "Cyrille",
"last_name": "MODIANO",
"email": "example@example.com",
"phone_number": "515.123.123",
"hire_date": "2017-11-06T18:00:00",
"job_id": "AD_VP",
"salary": 1000,
"commission_pct": null,
"manager_id": 100,
"department_id": 90
},{
"first_name": "Tom",
"last_name": "Kyte",
"email": "example1@example.com",
"phone_number": "515.124.124",
"hire_date": "2005-01-06T18:00:00",
"job_id": "AD_VP",
"salary": 50000,
"commission_pct": null,
"manager_id": 100,
"department_id": 90
},{
"first_name": "John",
"last_name": "Doe",
"email": "example2@example.com",
"phone_number": "515.123.123",
"hire_date": "2010-05-12T12:00:00",
"job_id": "AD_VP",
"salary": 1000,
"commission_pct": null,
"manager_id": 100,
"department_id": 90
}]
}
[oracle@srv-ora-01 tmp]$ curl -i -X POST --data-binary @/tmp/employee.json -H "Content-Type: application/json" http://192.168.1.50:8080/ords/hr/employees/insert_employees/ HTTP/1.1 100 Continue HTTP/1.1 200 OK Transfer-Encoding: chunked
SQL> select * from employees where last_name in ('MODIANO','Kyte','Doe');
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
226 John Doe example2@example.com 515.123.123 12-MAY-10 AD_VP 1000 100 90
225 Tom Kyte example1@example.com 515.124.124 06-JAN-05 AD_VP 50000 100 90
224 Cyrille MODIANO example@example.com 515.123.123 06-NOV-17 AD_VP 1000 100 90
This is the end of this introduction. Hope it helped you see the capabilities of Oracle Rest Data Services.
See you.
Hi guy ! Thank you for this cool tutorial, do you know if it’s possible to reply with other formats, like xml, or others ? ^^
Thanks in advance
Hi Vincent,
Thank you for reading my blog. I Know you can return csv by using:
p_source_type => ORDS.source_type_csv_query
For XML, I found that procedure:
CREATE OR REPLACE PROCEDURE P_SHOW_XML_TABLEIS
l_clob CLOB;
BEGIN
SELECT XMLELEMENT("article",
XMLFOREST( e.id as "id",
.....
)
).getClobVal()
INTO l_clob
FROM article e
where rownum = 1;
OWA_UTIL.mime_header('text/xml');
HTP.print(l_clob);
EXCEPTION
WHEN OTHERS THEN
raise;
END P_SHOW_XML_TABLE;
Then just create your service and pass this newly created procedure in the p_source parameter:
BEGIN
ords.create_service (
p_module_name => 'test.xmltable' ,
p_base_path => '/test/',
p_pattern => 'xmltable/',
p_method => 'GET',
p_source_type => ords.source_type_plsql,
p_source => 'begin p_show_xml_table; end;'
);
commit;
END;
I found this example Here
Thanks
Cyrille
Thank you Cyrille for the post.
Cheers,
Foued
Thanks Foued
Thank you Cyrille it’s a very good and clear introduction.
It’s been a long time since I wanted to know how REST was used with Oracle. I see now many applications.
Have a nice day my friend!
Thanks Vincent
Thanks Cyrille Modiano, I have same error: 404 when I install Oracle Rest Data Service . I find in metalink but can’t find anything or just explain not clearly . Thanks Modiano . Hopefully , you have many blog for Oracle Rest because it will be future of Oracle Database .
Do you have a 404 when you access your service?
Yes, luckily find your website, I understand and fix error. You have skype or something ? I can exchange about Oracle or something ?
You can use the contact form of this website if you need to contact me directly