In my previous blog Oracle REST Data Services – Installation and Configuration :
- We have installed ORDS in standalone mode.
- Configured ORDS to be administer by SQL Developer
In this blog, we will explain how to create simple RESTful Web Services using PL/SQL and a browser Rest Client..
My sources are :
First, let’s try to REST the schema/tables “HR/EMPLOYEES” using the Auto REST feature.
Auto REST feature allows to interact with schema/tables without writing any code.
The first step is to enable the REST Services for the schema HR :
In a productive mode, for security reason, “Schema alias” must be changed and “Authentication required” must be checked.
Let’s see the content of SQL code generated (taken from the SQL tab), you can execute this piece of code instead using graphical interface :
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 => FALSE); commit; END;
The second step is to enable the REST for Table Employees :
The SQL code related is :
DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN ORDS.ENABLE_OBJECT(p_enabled => TRUE, p_schema => 'HR', p_object => 'EMPLOYEES', p_object_type => 'TABLE', p_object_alias => 'employees', p_auto_rest_auth => FALSE); commit; END;
Now let’s try to access the EMPLOYEES table via /ords/hr/employees :
Let’s just explain how it works :
- We do GET on employees table, ORDS will find the appropriate database workload (here the SQL command : “SELECT * FROM EMPLOYEES”).
- ORDS return the output as formatted JSON (see the output above).
Let’s try now to create RESTful Web Services using PL/SQL:
Let’s create a REST Web Services using the GET method (Read/SELECT):
BEGIN ORDS.define_service( p_module_name => 'rest-v1', p_base_path => 'rest-v1/', p_pattern => 'countries/', p_method => 'GET', p_source_type => ORDS.source_type_collection_feed, p_source => 'SELECT * FROM countries', p_items_per_page => 0); COMMIT; END; /
Now let’s try to access the COUNTRIES table via /ords/hr/rest-v1/countries:
Let’s check the content of USER_ORDS_% views :
SELECT id, name, uri_prefix FROM user_ords_modules ORDER BY name; ID NAME URI_PREFIX ---------- --------------------- 10060 rest-v1 /rest-v1/ SELECT id, module_id, uri_template FROM user_ords_templates ORDER BY module_id; ID MODULE_ID URI_TEMPLATE ---------- ---------- ------------ 10061 10060 countries/ SELECT id, template_id, source_type, method, source FROM user_ords_handlers ORDER BY id; ID TEMPLATE_ID SOURCE_TYPE METHOD SOURCE ---------- ----------- --------------------------------------------------------- 10062 10061 json/collection GET SELECT * FROM countries
Now let’s create a POST Web Services (Create/INSERT):
First create a PL/SQL procedure to create new rows :
CREATE OR REPLACE PROCEDURE INS_EMP ( p_employee_id IN employees.employee_id%TYPE, p_first_name IN employees.first_name%TYPE, p_last_name IN employees.last_name%TYPE, p_email IN employees.email%TYPE, p_phone_number IN employees.phone_number%TYPE, p_hire_date IN VARCHAR2, p_job_id IN employees.job_id%TYPE, p_salary IN employees.salary%TYPE, p_commission_pct IN employees.commission_pct%TYPE, p_manager_id IN employees.manager_id%TYPE, p_department_id IN employees.department_id%TYPE ) AS BEGIN INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, HIRE_DATE, job_id, salary, commission_pct, manager_id, department_id) VALUES (p_employee_id, p_first_name, p_last_name, p_email, p_phone_number, TO_DATE(p_hire_date, 'YYYY-MM-DD'), p_job_id, p_salary, p_commission_pct, p_manager_id, p_department_id); EXCEPTION WHEN OTHERS THEN HTP.print(SQLERRM); END; /
The next step is to create the web service with a POST handler calling the stored procedure, passing the the parameters needed by the PL/SQL procedure :
BEGIN ORDS.define_module( p_module_name => 'rest-v4', p_base_path => 'rest-v4/', p_items_per_page => 0); ORDS.define_template( p_module_name => 'rest-v4', p_pattern => 'employees/'); ORDS.define_handler( p_module_name => 'rest-v4', p_pattern => 'employees/', p_method => 'POST', p_source_type => ORDS.source_type_plsql, p_source => 'BEGIN INS_EMP(p_employee_id => :employee_id, p_first_name => :first_name, p_last_name => :last_name, p_email => :email, p_phone_number => :phone_number, p_hire_date => :hire_date, p_job_id => :job_id, p_salary => :salary, p_commission_pct => :commission_pct, p_manager_id => :manager_id, p_department_id => :department_id); END;', p_items_per_page => 0); COMMIT; END;
We have to use a Rest Client to pass the parameters expected by the POST method (Advanced Rest Client extension for Chrome or RestClient plugin for Firefox).
The web service now can be called using :
- The URL : http://192.168.1.49:8080/ords/hr/rest-v4/employees/
- Method : POST
- Headers : Content-Type : application/json
- Body – remember the web server send JSON format : {“employee_id”:9998,”first_name”:”LAZHAR”,”last_name”:”FELAHI”,”email”:”LAZOO”,”phone_number”:”777″,”hire_date”:”2020-01-01″,”job_id”:”IT_PROG”,”salary”:5000,”commission_pct”:null,”manager_id”:201,”department_id”:10}
The goal of this POST method is to create the new employee_id “9998” into the table EMPLOYEES.
Let’s check if the employee exist before to execute the POST method :
select * from employees where employee_id = '9998'; no rows selected
Let’s execute the POST method by clicking on the SEND Button from the REST Client :
Check the POST method execution is successful (Green Button with message 200 OK).
Let’s check into the database if the new employee is created :
select * from employees where employee_id = '9998'; EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DAT JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID ----------- -------------------- ------------------------- ------------------------- -------------------- -------- ---------- ---------- -------------- ---------- ------------- 9998 LAZHAR FELAHI LAZOO 777 01.01.20 IT_PROG 5000 201 10
Conclusion :
- A lot of features exists with ORDS RESTful Web Services. In this blog, we just use standard HTTP calls via GET (Select/Read) and POST (Create/Insert) method interacting with the database.
- More complex RESTfull API can be written : Web Service with multiple value parameters, uploads files as BLOBS, create API interacting with your mobile phone.